Convert multiple rows into single row with more columns

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.

Answer

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);