convert multiple rows to columns

Question

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?

You should tag ONLY the DBMS you are using, not all of them. — Sean Lange, Aug 11 '22 at 17:07
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Dan Def, Aug 12 '22 at 07:33

John Cappelletti · Accepted Answer · 2022-08-12T17:44:52.920

Assuming GAV is NOT sequential as presented, we'll have to use the window function row_number() and some Dynamic SQL

Example or dbFiddle

Declare @SQL varchar(max)

Select @SQL = string_agg( concat('[',ColName,ColNr,']','=','max(case when ColNr =',ColNr,' then ',ColName,' end)') , ',') within group (ORDER BY ColNr,ColName Desc)
  From  (values ('Nutrient'),('GAV') ) A(ColName)
  Cross Join ( Select Distinct ColNr = row_number() over( partition by ItemID order by GAV) from YourTable ) B

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)

Results

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)

I am using 2016 version, do we have any alternative for string_agg please — Learner, Aug 12 '22 at 17:37
@Learner See Update 2016 Version or https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=db8722125c173f3dd31ed8d61537ed4d — John Cappelletti, Aug 12 '22 at 17:45

convert multiple rows to columns

1 Answers1