swordgit swordgit - 3 years ago 120
SQL Question

SQL Server 2008 pivoting table with unknown column name

I am having trouble to pivot the following

FirstName LastName
----------------------
Talyor Swift
Bruno Mars


to the following

ColumnName ColumnValue
--------------------------
FirstName Talyor
LastName Swift
FirstName Bruno
LastName Mars


I don't have any clue how to start this without hardcoding it especially the way to retrieve the column name from the system.

**Column Name in the source table is not given

Answer Source

Here is a "dynamic" approach which is accomplished via XML.

Clearly UNPIVOT would be more performant

Example

Select C.*
 From  YourTable A
 Cross Apply (Select XMLData = cast((Select A.* for XML Raw) as xml) ) B
 Cross Apply (
                Select Item   = attr.value('local-name(.)','varchar(100)')
                      ,Value  = attr.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row') as n(r)
                 Cross Apply n.r.nodes('./@*') AS B(attr)
             ) C

Returns

Item        Value
FirstName   Talyor
LastName    Swift
FirstName   Bruno
LastName    Mars

EDIT - Dynamic UnPivot

Declare @SQL varchar(max) ='
Select Item,Value
 From  YourTable
 UnPivot (Value for Item in ('+Stuff((Select ',' +QuoteName(Name) 
                                       From  sys.columns 
                                       Where objecT_id = OBJECT_ID('YourTable') 
                                       For XML Path ('')),1,1,'')
                             +')) as UnPiv
'
--Print @SQL
Exec(@SQL)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download