Nimble Fungus Nimble Fungus - 1 year ago 75
SQL Question

Need horizontal columns in vertical

I have compiled the data in a table called Employees. Table definition is

Name Age
Sam 25
Mike 28

Is it possible to write a query that can give me the output in format


I am unable to write this query. Is it possible to do it.
If not, how can i achieve that.
I can do it using a cursor but it will largely degrade the performance of my proc.

Answer Source

Easily modified to suite your needs

Declare @User table (id int,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @User values
(2,'Jane','Doe'  ,'')

Declare @XML xml
Set @XML = (Select * from @User for XML RAW)

Select ID    = r.value('@id','int')
      ,Item  = Attr.value('local-name(.)','varchar(100)')
      ,Value = Attr.value('.','varchar(max)') 
 From  @XML.nodes('/row') as A(r)
 Cross Apply A.r.nodes('./@*[local-name(.)!="id"]') as B(Attr)


ID  Item        Value
1   First_Name  John
1   Last_Name   Smith
1   EMail
2   First_Name  Jane
2   Last_Name   Doe
2   EMail
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download