Convert Rows into Columns in SQL Server 2005

Posted by Laghaterohan under Sql Server on 11/26/2011 | Points: 10 | Views : 12166 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hello,

I a column Subjects under which I have 6 rows that list various subject list eg:

Subject
---------
A
B
C
D
E
F

However, I want these rows to be displayed as Columns. However, I want it to be dynamic. Say if tomorrow a new row is added say G then that too should be dynmically be used as a column.

Please help me out..How can i do this? Using Pivot?

Regards,
Rohan

Best Regards,
Rohan Laghate

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: PandianS on: 11/26/2011 [Member] [MVP] Silver | Points: 25

0	Hi Let's take the following sample... (Table and Data) Create Table Table1 ( Id Int Identity(1,1), Subjects Varchar(10) ) Go Insert Table1(Subjects) Values('A') Insert Table1(Subjects) Values('B') Insert Table1(Subjects) Values('C') Insert Table1(Subjects) Values('D') Insert Table1(Subjects) Values('E') Insert Table1(Subjects) Values('F') Dynamic script Declare @DynamicSQL Varchar(Max), @Columns Varchar(Max) Select @Columns = Coalesce(@Columns +',','') + Subjects From Table1 Select @DynamicSQL = ' Select ' + @Columns + ' From (SELECT Id,Subjects FROM Table1) P PIVOT ( Count(Id) For Subjects In (' + @Columns + ') )AS PVT' Exec(@DynamicSQL) Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Laghaterohan, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

Let's take the following sample... (Table and Data)

Create Table Table1

(

Id	Int Identity(1,1),

Subjects	Varchar(10)

)

Go



Insert Table1(Subjects) Values('A')

Insert Table1(Subjects) Values('B')

Insert Table1(Subjects) Values('C')

Insert Table1(Subjects) Values('D')

Insert Table1(Subjects) Values('E')

Insert Table1(Subjects) Values('F')

Dynamic script

Declare @DynamicSQL	Varchar(Max),

		@Columns	Varchar(Max)



Select @Columns = Coalesce(@Columns +',','') + Subjects From Table1



Select @DynamicSQL = '

Select ' + @Columns + ' 

From

(SELECT Id,Subjects FROM Table1) P

PIVOT

(

Count(Id)

For Subjects In (' + @Columns + ')

)AS PVT'



Exec(@DynamicSQL)

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Laghaterohan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/25/2012 [Member] Starter | Points: 25

0	create FUNCTION toCSV (@id int) RETURNS varchar(100) AS BEGIN DECLARE @List varchar(100) SELECT @List = COALESCE(@List + ', ', '') + CAST(name AS varchar(10)) FROM aTable WHERE ID = @id RETURN(@list) END; go regards sriram Laghaterohan, if this helps please login to Mark As Answer. \| Alert Moderator

create  FUNCTION toCSV (@id int)



RETURNS varchar(100)



AS



BEGIN



DECLARE @List varchar(100)



SELECT @List = COALESCE(@List + ', ', '') + 

   CAST(name AS varchar(10))



FROM aTable



WHERE ID = @id



RETURN(@list)



END;



go

regards

sriram

Laghaterohan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 6/8/2012 [Member] Starter | Points: 25

0	http://www.c-sharpcorner.com/uploadfile/suthish_nair/convert-rows-to-columns-in-sql-server-20082005/ ER sandeep chourasia sandeepchrs@yahoo.com (on facebook) http://www.aspnetcodes.com/ Laghaterohan, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts