Partial transpose of rows to columns

Question

The question is very simple, but I am having troubles with realization.

The current table looks like this:

ID  Value

A   1
A   2
A   3
B   1
B   2
C   1

And i need it like this:

ID Value1 Value2 Value3 Value...
A   1      2      3      NULL
B   1      2      NULL   NULL
C   1     NULL    NULL   NULL

score 1 · Answer 1 · edited May 23 '17 at 12:33

1

If the value columns are a known\reasonable set range i.e. 1-5 you can do something like the following:

Select ID, 
    MAX(CASE WHEN Value = 1 Then 1 Else Null End) as Value1,
    MAX(CASE WHEN Value = 2 Then 2 Else Null End) as Value2,
    MAX(CASE WHEN Value = 3 Then 3 Else Null End) as Value3,
    MAX(CASE WHEN Value = 4 Then 4 Else Null End) as Value4,
    MAX(CASE WHEN Value = 5 Then 5 Else Null End) as Value5
From Table
Group By ID

If you don't know the number of columns at the outset i.e. they are dynamic you will then have to write a dynamic sql pivot. There are plenty of stack examples showing this:

edited May 23 '17 at 12:33

Community

1
1

answered Mar 09 '15 at 16:48

sarin

5,227
3
34
63

Hi, thanks for this, but as a novice, i still cant get a grip on that. The problem is that my example was really simplified. ID and Value fields are basicaly random character strings. so for id 22845, Value1 can be 1323jk3432 and value2 ddffjedi772. So I can not use case when value =. I know that there is a max of 10 value columns. So in plain words, the query should enter the 1st value for ID1 into Value1 field. After that, the next one into the Value2, if it is not already in Value1. Maybe to use count, i really dont know. – Peraklo Mar 10 '15 at 08:26
So your columns are random, in which case you need to do a dynamic pivot. Check the links i have given you for examples – sarin Mar 10 '15 at 08:58

score 0 · Accepted Answer · answered Mar 10 '15 at 10:22

OK, with your help, and a help from a friend, i solved the problem like this.

Select ROW_NUMBER() 
        OVER (PARTITION BY Field1 
        ORDER BY Field1) 
        AS order_num, Field1, Value
into #tab1
from Source_Table
Order by Field1


Select * 
from #tab1
    PIVOT
        (Max(Value)  
            FOR order_num IN ([1], [2], [3], [4], [5])) AS PVT


drop table #tab1

I still have to fully understand how it works, but it works. I hope it helps someone else too.

Partial transpose of rows to columns

2 Answers2