How can I combine 2 rows to 1 row in SQL?-CodePudding

I have some data like this:

ID	Color
1	Pink
1	Blue
2	Red
2	Green

I want it to look like this:

ID	Color1	Color2
1	Pink	Blue
2	Red	Green

Any help is appreciated! Thank you!

CodePudding user response：

If you have a known, or maximum number of columns a simple PIVOT or conditional aggregation should do the trick, otherwise, you would need Dynamic SQL

Example PIVOT

Select *
 From  ( Select ID
               ,Col = concat('Color',row_number() over (partition by ID order by ID)
               ,Val = Color
         From  YourTable
       ) src
 Pivot (max(Val) for Col in ([Color1]
                            ,[Color2]
                            ) 
       ) pvt

Example Conditional Aggregation

Select ID
      ,Color1 = max(case when RN=1 then Color end )
      ,Color2 = max(case when RN=2 then Color end )
 From (
        Select ID
              ,Color
              ,RN = row_number() over (partition by ID order by ID)
         From  YourTable
      )  A
 Group By ID

Note:

The order by ID portion in row_number() could be any other column like Color ascending or descending.

CodePudding user response：

Lookup the PIVOT clause for SQL Server. You can rotate rows into columns.