CODEWITHSUNDEEP
sqlt-sqlsql-server-2014
Peraklo
Peraklo

Reputation: 79

Partial transpose of rows to columns

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

Upvotes: 0

Views: 774

Answers (2)

Peraklo
Peraklo

Reputation: 79

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.

Upvotes: 0

sarin
sarin

Reputation: 5307

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:

Upvotes: 1

Related Questions