SQLTeam.com | Weblogs | Forums

Columns to rows help

HI,
In one of the table 600 columns are there and one row is there ,Need to transform data in two columns like below.
select * from abc
a1 a2 c3...600 columns(a1,a2,a3 columns names)
asa def xyz

should be like using any single query or dynamic query
a1 asa
a2 def
a3 xyz
......
.
.
.
.

Hello krajasekharv
please provide real consumable data as follows

create table #sampledata(a1 int, a2 int, a3 int)

insert into #sampledata
select 1, 1, 1 union
select 2, 2, 2 union
select 3,3,3

of course provide real data this was just a sample

Have a look (Google) at what UNPIVOT can do.

	if object_id('tempdb..#temp') is not null drop table #temp

	create table #temp(

	 a1 varchar(255)
	,a2 varchar(255)
	,c3 varchar(255)

	) 

	insert into #temp
	select 'asa','def','xyz'


	select * from #temp


	select ColumnHeadings,Answers
	from #temp unpivot (Answers for ColumnHeadings in ([a1],[a2],[c3]
	--,[c4],[c5],[c6],.......[c600]
	))unpvt

hi

there is
information_schema.columns

Here you can write a query for 600 Columns ..

select
column_name
from
information_schema.columns
where
table_name = 'TABLE'

https://www.mssqltips.com/sqlservertutorial/183/informationschemacolumns/