Tuesday, October 13, 2009

Transposing Column to Row Through Select Query

My student Mr. Bhupendra Asked me that how can i show values of my columns of table as a ROW. To do so: (we also require such query while creating cross-tab report
lets we have following table:

CREATE TABLE [vehical_type](
[unqid] [uniqueidentifier] primary key,
[vname] [varchar](100) NULL
)

we can get our desired result from below query:
-- It will be better to create Stored Procedure of it.
declare @st varchar(max)
set @st='create table tmp ('
-- instead of taking normal table we can take temporary table by using #tmp
select @st = @st + ' temp' + convert(varchar,ROW_NUMBER() OVER(ORDER BY vname )) + ' varchar(max),' from vehical_type order by vname
set @st = left(@st,len(@st)-1) + ');'
--exec(@st)
--select @st
set @st= @st + 'insert into tmp values ('
select @st = @st + '''' + CONVERT(varchar(max), unqid) + ''',' from vehical_type
set @st = left(@st,len(@st)-1) + ');'

set @st= @st + 'insert into tmp values ('
select @st = @st + '''' + vname + ''',' from vehical_type
set @st = left(@st,len(@st)-1) + ');'
--select @st
exec(@st)
select * from tmp
drop table tmp

Share This!


No comments:

Powered By Blogger · Designed By Seo Blogger Templates