Source:
| ItemId | ItemName | Nutrient | GAV |
|---|---|---|---|
| A | Beef | Vit A | 1 |
| A | Beef | Vit B | 2 |
| A | Beef | Vit C | 3 |
target:
| Id | Name | Nut1 | GAV1 | Nut2 | GAV2 | Nut3 | GAV3 |
|---|---|---|---|---|---|---|---|
| A | Beef | VitA | 1 | VitB | 2 | VitC | 3 |
How can we achieve this with ms-sql query?
CodePudding user response：
Assuming GAV is NOT sequential as presented, we'll have to use the window function row_number() and some Dynamic SQL
Example or 
UPDATE 2016 Version
Declare @SQL varchar(max) = ''
Select @SQL = @SQL concat(',','[',ColName,ColNr,']','=','max(case when ColNr =',ColNr,' then ',ColName,' end)')
From (values ('Nutrient'),('GAV') ) A(ColName)
Cross Join ( Select Distinct ColNr = row_number() over( partition by ItemID order by GAV) from YourTable ) B
Order By ColNr,ColName Desc
Set @SQL = '
Select ItemID
,ItemName
' @SQL '
From ( Select *
,ColNr = row_number() over( partition by ItemID order by GAV )
From YourTable
) A
Group By ItemID
,ItemName
'
Exec(@SQL)