Convert Rows into Columns in SQL Server 2005

Posted by Laghaterohan under Sql Server on 11/26/2011 | Points: 10 | Views : 11209 | Status : [Member] | Replies : 3
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



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response