Convert Rows into Columns in SqlServer

convert row into columns in sql.It is very Important and common scenario when we need to convert sql table row into sql table Columns.

it is frequently used in Reporting.

create Table HeadMaster
(
headid int primary key,
headname nvarchar(50)
)
insert into HeadMaster values (1,'Head1')
insert into HeadMaster values (2,'Head2')
insert into HeadMaster values (3,'Head3')

HeadMaster table have the three rows.with have the headname Head1,Head2 and Head3.

We want another table DemoTable having columns Head1,Head2…..

lets Begin..

Declare @HeadNameList nvarchar(max)
Declare @HeadName nvarchar(50)
--create comma sepreted list of headname column
select  @HeadNameList = coalesce(@HeadNameList + ',', '') + cast(headname as nvarchar(50)) from HeadMaster
set @HeadNameList = @HeadNameList + ','
--select @HeadNameList
if Object_ID('DemoTable') is not null drop table DemoTable
Create Table DemoTable
(
[S.No] int
)
while(charindex(',',@HeadNameList)>0)
Begin
select @HeadName = Substring(@HeadNameList,1,Charindex(',',@HeadNameList)-1)
select @HeadNameList = Substring(@HeadNameList,Charindex(',',@HeadNameList)+1,4000)
exec('alter table DemoTable add [ '+ @HeadName+ ']nvarchar(50)')
End
select * from DemoTable
Advertisements
This entry was posted in Sql. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s