透视sql server多个表和列

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.

这是我第一次来这里。一般来说,我发现自己有一些答案,但现在我遇到了PIVOT sql server 2012的一个大问题。我只是一个实习生而且我不知道如何使用PHP在多个表和列中做PIVOTS。所以我有3个表(Equipements,Champ-perso,Equipement_Complément),我成功地通过使用PHP的SQL查询显示所有数据。但是在这个显示中,有一些重复的设备,并且Champ-Perso和Equipement_Complément出现在行中。我想做一个功能PIVOT删除设备重复,并在列中显示Champ-Perso和Equipement_Complément。

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
`

1 个解决方案

#1

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

这里是您的问题的解决方案。我假设你问题中显示的数据在表#temp中。

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

首先,你需要从你的桌子上找到独特的Champ-Perso。使用以下查询。

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.

注意:由于Champ-Perso在您的Name_Equipement中并不常见,因此您将看到NULL,因为该值将丢失。

#1