How to convert table columns to rows

Question

I have a table like below:

+-----------+------------+--------+
|   Col1    |    Col2    |  Col3  |
+-----------+------------+--------+
|  12345678 | FirstName  | John   |
|  12345678 | LastName   | Smith  |
| 987456321 | LastName   | Clancy |
| 987456321 | MiddleName | T      |
| 987456321 | Height     | 176cm  |
| 654125878 | FirstName  | Tan    |
| 654125878 | Weight     | 150lb  |
+-----------+------------+--------+

How to convert that to:

+-----------+-----------+------------+----------+--------+--------+
|    ID     | FirstName | MiddleName | LastName | Height | Weight |
+-----------+-----------+------------+----------+--------+--------+
|  12345678 | John      | null       | Smith    | null   | null   |
| 987456321 | null      | T          | Clancy   | 176cm  | null   |
| 654125878 | Tan       | null       | null     | null   | 150lb  |
+-----------+-----------+------------+----------+--------+--------+

John Cappelletti · Accepted Answer · 2016-12-24T00:36:16.480

I think a conditional aggregation would do the trick here.

Assuming you don't need dynamic

Select ID = Col1
      ,FirstName   = max(case when Col2='FirstName'   then Col3 else null end)
      ,MiddleName  = max(case when Col2='MiddleName ' then Col3 else null end)
      ,LastName    = max(case when Col2='LastName '   then Col3 else null end)
      ,Height      = max(case when Col2='Height'      then Col3 else null end) 
      ,Weight      = max(case when Col2='Weight'      then Col3 else null end) 
 From YourTable
 Group By Col1

If you need dynamic

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName([Col2]) From Yourtable  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select [Col1] as ID ,' + @SQL + '
From   YourTable
 Pivot (max(Col3) For [Col2] in (' + @SQL + ') ) p'
Exec(@SQL);

If it helps, the generated SQL for the dynamic Pivot is as follows:

Select [Col1] as ID ,[FirstName],[Height],[LastName],[MiddleName],[Weight]
From   YourTable
Pivot  (max(Col3) For [Col2] in ([FirstName],[Height],[LastName],[MiddleName],[Weight]) ) p

How to convert table columns to rows

1 Answers1