Hide SQL String Property Result

Question

SQL Server 2005

I have the following result set:

ID name prop value -------------------------- 1 one Prop1 va1_1_1 1 one Prop2 val_1_2 2 two Prop1 val_2_1 2 two Prop2 val_2_2 3 three Prop2 val_3_2 4 four Prop1 val_4_1 4 four Prop2 val_4_2 

How can I flatten it to get output

 ID name Prop1 Prop2 --------------------------------- 1 one val_1_1 val_1_2 2 two val_2_1 val_2_2 3 three val_3_2 NULL 4 four val_4_1 val_4_2 

Note: The number of properties ( Prop1 , Prop2 ) is arbitrary and can be many.

Answer 1

See MSDN: Using PIVOT and UNPIVOT .

He has a very good example of what you are trying to do.

This will give you the desired result.

 Select * From ( Select ID,name,prop,value from YourTable ) P PIVOT ( max(value) For Prop in (Prop1,Prop2) ) as pvt Order By ID 

Please note: you need to support an arbitrary number of prop values. In this case, one solution could be to create this script dynamically and execute it.

EDIT: To do this, here is the SQL that will work for an arbitrary number of prop values ​​-

 Declare @Value as NVarChar(Max) Set @Value = 'Select * From ( Select ID,name,prop,value from YourTable ) P PIVOT ( max(value) For Prop in (' Select @Value = @Value + Prop + ',' from ( Select Distinct Prop From YourTable) YT Set @Value = Left(@Value, Len(@Value)-1) Set @Value = @Value + ') ) as pvt Order By ID' Exec(@Value) 

Answer 2

  SELECT ID,Name, (SELECT prop FROM table t1 WHERE prop=prop1 AND ID = table.id) AS Prop1, (SELECT prop FROM table t2 WHERE prop=prop2 AND ID = table.id) AS Prop2 FROM table 

Answer 3

This is what you are looking for:

• Dynamic column rotations in SQL Server 2005/2008
• SQL Dynamic Pivot - how to arrange columns