CODEWITHSUNDEEP
sqlsql-serversql-server-2012unpivot
zanq
zanq

Reputation: 115

UNPIVOT Table Columns

I want to get my table data in rows instead of columns.

Dynamically read the column names too.

Visual example:

ColA    ColB    ColC    ColD
1       2       3       4

To this:

ColA    1
ColB    2
ColC    3
ColD    4

Upvotes: 1

Views: 59

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

This approach will "dynamically" unpivot your data without having to actually use Dynamic SQL or specify all the field names.

Full Disclosure: Gordon's approach is certainly more performant.

Example

Select C.*
 From  YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('Colums','To_Exclude')
             ) C

Returns

Item    Value
ColA    1
ColB    2
ColC    3
ColD    4

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

My preferred method is apply:

select v.*
from t cross apply
     (values ('colA', t.colA), ('colB', t.colb), ('colC', t.colc), ('colD', t.cold)
     ) v(which, val);

Technically, apply implements a lateral join. This is very powerful; unpivot is just one of many things that it can accomplish. However, unpivot is a good way to get started in learning about apply.

Upvotes: 1

Related Questions