UNPIVOT Table Columns

Question

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

Answer 1

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