CODEWITHSUNDEEP
sqlsql-server
Sally
Sally

Reputation: 1

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

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!

Upvotes: 0

Views: 55

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

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.

Upvotes: 1

Related Questions