Pivot sql server multiple tables and columns

Question

it's my first time to come here. In generally i find myself some answers but now i got a big problem with PIVOT sql server 2012. I'm just an intern and i don't know how to do PIVOTS with Multiple Tables and Columns in using PHP. So i Have 3 Tables (Equipements, Champ-perso, Equipement_Complément), I succeeded to display all the data through a SQL query using PHP. But in this display, There are equipements which are duplicate, and Champ-Perso and Equipement_Complément appear in rows. And I want to do a function PIVOT which deletes equipements duplicate, and displays Champ-Perso and Equipement_Complément in columns.

Here is my display

`Name_Equipement     Champ-Perso               Equipemennt-Complément

ENG-0001         Année rénovation                  A
ENG-0001         Largeur utile (mm)                B
ENG-0001         Nb de pinces de dépose            C
ENG-0001         Nb de postes dengagement          D
ENG-0001         Nb de voies                       E
ENG-0001         Numéro du train                   F
ENG-0001         Type/modèle                       G
ENG-0002         Année rénovation                  A1
ENG-0002         Largeur utile (mm)                B1
ENG-0002         Nb de pinces de dépose            C1
ENG-0002         Nb de postes dengagement          D1
ENG-0002         Nb de voies                       E1
ENG-0002         Numéro du train                   F1
ENG-0002         Type/modèle                       G1
`

And i want to display

`Name_Equipment Année rénovation Largeur Utile (mm) ... Type:Modèle            
ENG-0001         A                 B                ...   G
ENG-0002         A1                B1               ...   G1
`

if Champ_perso has n (variable) values, then you must use dynamic sql such as http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query. If you know the champ-Perso will always be the same number (7 it appears in this case) then you can use a normal Pivot to do this. You simply hard code the camp-perso values into the pivot. . To help beyond this we would need to see the SQL, table structure. The desired results helps, but isn't sufficient without the SQL or table structures — xQbert, May 12 '15 at 14:16
the table structures is required. However, would `select DISTINCT` be enough ? — niceman, May 12 '15 at 14:20
Please can you tell me if my query is correct, cause in SQL server i got an error like " Impossible to add a table 'SELECT' ": SELECT EQUIPEMENTS.EQU_EQUIPEMENT, [EQU_COMPLEMENTS.EQC_VALEUR_A],[CHAMPS_PERSOS.CHP_DESIGNATION] FROM (My sql query) as src PIVOT ( FOR CHAMPS_PERSOS.CHP_DESIGNATION IN([Année rénovation], [Largeur utile (mm)], [Nb de pinces de dépose], [Nb de postes dengagement], [Nb de voies], [Numéro de train], [Type/modèle]) ) as Pv1 — GOLALI, May 12 '15 at 15:16

Amnesh Goel · Answer 1 · 2015-08-20T08:11:58.063

Here the solution for your problem. I assume that the data shown in your question is in table #temp.

First you need to find unique Champ-Perso from your table. Use below query.

select * into #temp1 from (select distinct([Champ-Perso]) from #temp) as A

Now write your pivot query as below...

Declare @str nvarchar(1000)
Declare @SQL nvarchar(1000)
SELECT @str = COALESCE(@str+'],[', '') + [Champ-Perso] FROM   #temp1
set @str = '[' +@str + ']'

set @SQL = N' select [Name_Equipement], ' + @str +' from (select [Name_Equipement], [Champ-Perso], [Equipemennt-Complément] from #temp) as tbl
Pivot
(
 max([Equipemennt-Complément])
 for [Champ-Perso] in ('+ @str+')
) as p'

To get the result, fire this query...

EXECUTE sp_executesql @sql

Note: Since Champ-Perso is not common across your Name_Equipement, so you will see NULL whereever the value would be missing.

Pivot sql server multiple tables and columns

1 Answers1