CODEWITHSUNDEEP
sqlsql-serverpivot
Learner
Learner

Reputation: 13

convert multiple rows to columns

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?

Upvotes: 1

Views: 86

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

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

enter image description here

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)

Upvotes: 1

Related Questions