Pivot sql server multiple tables and columns
this is my first time to come here. In general, I find some answers to some questions, but now I'm having a big problem with PIVOT sql 2012 server. I'm just a trainee and I don't know how to do PIVOTS with multiple tables and columns when using PHP. So I have 3 tables (Equipements, Champ-perso, Equipement_Complément), I was able to display all data via SQL query using PHP. But this display has duplicate devices and Champ-Perso and Equipement_Complément are shown in strings. And I want to execute a PIVOT function that removes duplicate equipements 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
`
source to share
Here is the solution for your problem. I am assuming the data given in your question is in the table #temp
.
First you need to find the unique one Champ-Perso
from your table. Use below query.
select * into #temp1 from (select distinct([Champ-Perso]) from #temp) as A
Now write your summary query like 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, run this query ...
EXECUTE sp_executesql @sql
Note: Since it is Champ-Perso
not usual for yours Name_Equipement
, then you will see NULL
if the value was missing.
source to share