Bester Bester - 9 months ago 51
SQL Question

Convert multiple rows to columns - Not using PIVOT

I know there are many posts about this, but I can't seem to find one that applies. I want to put values that are causing multiple rows into separate columns so that I only have one row of data. Here's a specific example:

SELECT ID, FavoriteColor
FROM MyColorTable

Since users can enter multiple favorite colors - Results:

ID FavoriteColor
856 Blue
856 Red
122 Purple
909 Blueish-Pink

I need it to format as (Note - I cannot use PIVOT):

ID FavoriteColor1 FavoriteColor2
856 Blue Red
122 Purple
909 Blueish-Pink

Can someone help me figure this out? Maybe with CASE statements? I've spent too many hours fiddling with my SQL.

Answer Source

Assuming you can't go dynamic, a conditional aggregation may help

Select ID
      ,FavoriteColor1 = max(case when RN=1 then FavoriteColor else '' end)
      ,FavoriteColor2 = max(case when RN=2 then FavoriteColor else '' end)
From  (
        Select *
              ,RN=Row_Number() over (Partition By ID Order by FavoriteColor)
         from YourTable
      ) A
Group By ID


ID  FavoriteColor1  FavoriteColor2
122 Purple  
856 Blue            Red
909 Blueish-Pink