Flatten SQL row 'properties' 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 an output of

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.

Is it always just `Prop1` and `Prop2` or is the number of properties dynamic so you need a dynamic amount of columns? — Mikael Eriksson, Jun 17 '11 at 17:59
Dynamic, so an arbitrary number of columns. Updated question. — Omar, Jun 17 '11 at 18:03

YetAnotherUser · Accepted Answer · 2011-06-17T19:41:52.143

3

See MSDN: Using PIVOT and UNPIVOT.

It 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 arbitrary number of prop values. In that case one solution could be to build this script dynamically and execute it.

EDIT: To make it complete, here is the SQL that would work for 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)

edited Jun 17 '11 at 19:41

answered Jun 17 '11 at 18:13

YetAnotherUser

9,156
3
39
53

I've seen that before. But it seems the example (and all other PIVOT examples) revolve around using some type of aggregate function (`SUM`, `MAX`). I don't see how I can apply that to this. – Omar Jun 17 '11 at 18:23
I've updated the answer to show how you can use MAX() to get what you want. – YetAnotherUser Jun 17 '11 at 18:57
That is not what OP is looking for. OP wants Pivot with Dynamic Columns. – Adriano Carneiro Jun 17 '11 at 19:01
@Ardian - That is why I've stated, OP will need to generate this script dynamically to support arbitrary number of prop values. – YetAnotherUser Jun 17 '11 at 19:21
This works. Final question, how can I have the query concatenate the content in `value`if there exists more than one row with the same `ID` and `prop`? – Omar Jun 17 '11 at 22:11
I don't think you can concatenate strings, it only works on [Aggregate Functions](http://msdn.microsoft.com/en-us/library/ms173454.aspx). You can get the same effect by concatenating the desired items in a temp table and pivoting this temp table instead. – YetAnotherUser Jun 17 '11 at 22:31

score 0 · Answer 2 · answered Jun 17 '11 at 18:03

0

    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

answered Jun 17 '11 at 18:03

Frank Pearson

872
5
16

1

you've got the prop1 alias used 2 times, and the question has been updated to state dynamic column numbers. – nathan gonzalez Jun 17 '11 at 18:05
I saw that the question after first submitting. Well in case something static could be useful in the end. – Frank Pearson Jun 17 '11 at 18:16

score 0 · Answer 3 · edited May 23 '17 at 12:13

0

This is what you are looking for:

edited May 23 '17 at 12:13

Community

1
1

answered Jun 17 '11 at 18:57

Adriano Carneiro

57,693
12
90
123

Flatten SQL row 'properties' result

3 Answers3