create table #t (col1 varchar(50), col2 varchar(50));
INSERT INTO #t VALUES ('as', 'c'),('ds', 'gf'),('sa', 'fr');
;with mycte
as
(
SELECT rn,cols,val
FROM (SELECT row_number() over(order by col1) rn ,col1,col2
FROM #t) AS src1
UNPIVOT (val FOR cols
IN ( [col1], [col2])) AS unpvt
)
SELECT [1] as Col1,[2] as Col2,[3] as Col3
FROM (SELECT rn,cols,val
FROM mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3])) AS pvt
drop table #t
Santosh J
MCP
Marked as answer by heyitsme on Jan 02, 2013 10:09 AM
heyitsme
Participant
1085 Points
399 Posts
sql query to convert rows into columns
Jan 01, 2013 08:34 AM|LINK
How can i write the query?
Regards
Sangamesh Belawatgi,
Software Developer,
Bangalore - INDIA
santosh.jagd...
Star
7625 Points
1454 Posts
Re: sql query to convert rows into columns
Jan 01, 2013 09:36 AM|LINK
here are 5 methods to convert row as column
http://sqlserveradvisor.blogspot.in/2009/03/sql-server-convert-rows-to-columns.html
here is sample code
create table #t (col1 varchar(50), col2 varchar(50)); INSERT INTO #t VALUES ('as', 'c'),('ds', 'gf'),('sa', 'fr'); ;with mycte as ( SELECT rn,cols,val FROM (SELECT row_number() over(order by col1) rn ,col1,col2 FROM #t) AS src1 UNPIVOT (val FOR cols IN ( [col1], [col2])) AS unpvt ) SELECT [1] as Col1,[2] as Col2,[3] as Col3 FROM (SELECT rn,cols,val FROM mycte) AS src2 PIVOT ( Max(val) FOR rn IN ([1], [2], [3])) AS pvt drop table #tMCP
dhol.gaurav
Contributor
3998 Points
725 Posts
Re: sql query to convert rows into columns
Jan 01, 2013 09:37 AM|LINK
Hi,
Check following link it may help to to convert row to column into sql server, convert row to column it called PIVOT in sql server
http://stackoverflow.com/questions/10944910/how-to-convert-a-column-header-and-its-value-into-row-in-sql
let me know if any query
Gaurav Dhol
Skype ID : dhol.gaurav
If My Post contains helped you, Please Mark as Answer
0ramramram0
Member
305 Points
73 Posts
Re: sql query to convert rows into columns
Jan 01, 2013 09:43 AM|LINK
hai use datatable .Below link is help full for you
http://www.codeproject.com/Articles/44274/Transpose-a-DataTable-using-C
Good luck~
http://forums.asp.net/t/1822478.aspx/1