Pivot Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot Table

Author

Topic

gyana.ojha
Starting Member

5 Posts

Posted - 2009-11-14 : 07:13:42

Hello Forum Member

I have a table Class Syllabus with Following Data.

quote:

Class Subject Is Covered
Mon-1 Math Yes
Mon-1 Eng Yes
Mon-2 Math Yes
Mon-2 Eng No
Mon-3 Math Yes
Mon-3 Eng Yes
. . .
. . .
. . .
N1 N1 Yes

And I want result as mention below

quote:

Class Math English . . . .N1
Mon-1 YES YES
Mon-2 YES NO
Mon-3 YES YES
. YES NO
.
.
.
N1

Thanks and Regards
Gyana Ranjan Ojha

ranganath
Posting Yak Master

209 Posts

Posted - 2009-11-15 : 23:24:48

Hi Try with this

Create Table #T
(
Class VARCHAR(128),
Sub VARCHAR(128),
covered VARCHAR(128)
)
INSERT INTO #T
SELECT 'Mon-1','Math','Yes' Union All
SELECT 'Mon-1','Eng','Yes' Union All
SELECT 'Mon-2','Math','Yes' Union All
SELECT 'Mon-2','Eng','No' Union All
SELECT 'Mon-3','Science','Yes' Union All
SELECT 'Mon-3','Math','Yes' Union All
SELECT 'Mon-3','Eng','No' Union All
SELECT 'Mon-4','Other','Yes'

--SELECT * From #T

DECLARE @Sql VARCHAR(8000)
SET @sql = ''
DECLARE @str VARCHAR(8000)
SET @str = ''

SELECT @sql = @sql + ', Min(Case when Sub = ''' +Sub + ''' Then covered End ) AS "' + Sub + '"'
From (SELECT distinct Sub From #T)a

SELECT @str = @str + 'SELECT Class '+@sql+' From #T Group By Class'
print @str
EXEC (@str)

DROP TABLE #T

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-11-15 : 23:24:49

Hi Try this once,

Select Class,math, English ---, N1
from table
pivot( max(covered) for subject in ( math, English ---, N1 )) p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources