Pivoting the table and show column as rows-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

what are you looking is Unpivot not Pivot. Use Unpivot as follows. make sure that all columns have same datatype.

 select
  colname,
  value
from @tab1
unpivot
(
  value
  for colname in ([name]
      ,[mobile]
      ,[address]
     )
) unpiv;