Columns into rows in sql server

SQL Server 2005 is providing a cool feature Unpivot to convert columns data into rows. But, in our legacy system we dont have this feature. Inturn, we have to create some temporary tables to achieve the same. I have tried it to convert columns into rows. There are thousands of ways to achieve this task. I have tried one among them,

Code Snippet

create table aaaa(id int,nam varchar(10),city varchar(10)) insert into aaaa values(1,'aa','sydney') insert into aaaa values(2,'bb','delhi') insert into aaaa values(3,'cc','chennai') select * from aaaa create table #temp1(id int, nam varchar(10)) insert into #temp1 select id,nam from aaaa create table #temp2(id int, nam varchar(10)) insert into #temp2 select id,city from aaaa

Below is the ouput we are getting for the table aaaa


We are trying to convert it into rows, so the output should resemble similar to the below screenshot.

Code Snippet

select id,nam from #temp1 union all select * from #temp2 order by #temp1.id

Happy Learning!!!.
Regards,
Venkatesan Prabu. J