Nimble Fungus Nimble Fungus - 3 months ago 11
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

SAM
25
MIKE
28


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

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
(1,'John','Smith','john.smith@gmail.com'),
(2,'Jane','Doe'  ,'jane.doe@gmail.com')

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)

Returns

ID  Item        Value
1   First_Name  John
1   Last_Name   Smith
1   EMail       john.smith@gmail.com
2   First_Name  Jane
2   Last_Name   Doe
2   EMail       jane.doe@gmail.com
Comments