Search code examples
sqlsql-serversql-server-2012

Pivoting the table and show column as rows

I have a table, I want to pivot the table, my desired output is @tab2.

My table is as follows:

declare @tab1 table(name varchar(50),mobile varchar(10),address varchar(100))
insert into @tab1 values('Test','2612354598','CG-10')
select * from @tab1

My desired output is:

declare @tab2 table(colname varchar(50),value varchar(100))
insert into @tab2 values('name','Test'),('mobile','2612354598'),('address','CG-10')
select * from @tab2

Please help

Solution

  • If using SQL server you can use UNPIVOT.

    SELECT colname, valueid
 FROM   
(SELECT CAST(name as varchar(100)) name, CAST(mobile as varchar(100)) 
 mobile, address FROM @tab1) p  
UNPIVOT  
 (valueid FOR colname IN   
  (name,mobile, address)  
 )AS unpvt;

    You need to CAST() the columns so the type is the same for all of them.