Convert multiple rows into single row with more columns

Question

I have a SQL Server table of properties related by an id and want to do a query to check if an id has a set of properties. Basically I want to do a transform as follows, grouping by the Id and adding a column for each unique property:

Starting table:

Id	Prop	Value
1	P1	V1
1	P2	V2
1	P3	V3
2	P1	V4
2	P2	V5
2	P3	V6

Resulting table:

Id	P1	P2	P3
1	V1	V2	V3
2	V4	V5	V6

Is it even possible to convert row values to column names? I'm pretty new to SQL and didn't design this initial table configuration. Thus far my attempts to use pivot and group by have been unsuccessful.

If you know in advance how many columns you want this is perfectly possible. It's called "pivoting". However, if you don't know the number of columns in advance, you won't be able to have separate columns; you can still concatenate them into a single one, though. — The Impaler, Jul 24 '21 at 00:16
Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Stu, Jul 24 '21 at 08:17

John Cappelletti · Accepted Answer · 2021-07-24T03:40:24.513

Several options here.

You may notice that I listed the 3 column in the subquery. You need to "feed" your PIVOT with only the required columns.

Known Columns to Pivot

Select *
 From  (
        Select ID
              ,Prop
              ,Value
         From YourTable
       ) src
 Pivot (max(Value) for Prop in ([P1],[P2],[P3]) ) pvt

Dynamic Pivot and version <2017

Declare @SQL varchar(max) = '
Select *
 From (
        Select ID
              ,Prop
              ,Value
         From YourTable
      ) A
 Pivot (max([Value]) For [Prop] in (' + stuff((Select Distinct ','+QuoteName(Prop) 
                                                From  YourTable
                                                Order By 1 
                                                For XML Path('')),1,1,'')  + ') ) p'
Exec(@SQL);

Dynamic Pivot for 2017+

Declare @SQL varchar(max) = '
Select *
 From (
        Select ID
              ,Prop
              ,Value
         From YourTable
      ) A
 Pivot (max([Value]) For [Prop] in (' + (Select string_agg(quotename(Prop),',') 
                                          From (Select distinct Prop From YourTable ) A) + ') ) p'
Exec(@SQL);

Convert multiple rows into single row with more columns

1 Answers1