generate query - 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)

generate query

Author

Topic

gyana.ojha
Starting Member

5 Posts

Posted - 2010-01-07 : 02:20:56

Hello

I have requirement like I am maintaining a student monthly exam report ..

I have a table tblExam where data containing like this..

Class Month heldornot

Class1 Jan Yes

Class1 Feb Yes

Class1 Mar Yes

Class1 April No

Class2 Jan Yes

Class2 Feb yes

Class2 MAR No

Class2 April No

Now if I will generate 2 Exam report from 1) Jan to March 2) Jan to April

then result will be

Report 1

Class Jan Feb March

Class 1 Yes Yes Yes

Class 1 Yes Yes No

Report 2

Class Jan Feb March April

Class 1 Yes Yes Yes Yes

Class 1 Yes Yes No No

I want to display above report in gridview...

Thanks and Regards
Gyana Ranjan Ojha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 02:32:39

make use of pivot


SELECT Class,Jan,Feb,Mar,Apr
FROM table t
PIVOT (MAX(heldornot) FOR [Month] IN (Jan,Feb,Mar,Apr))p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 02:33:37

and use this if you want to do it dynamically

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

gyana.ojha
Starting Member

5 Posts

Posted - 2010-01-07 : 02:45:11

Thank you but month will based on selection of month from and to
Like if user select month from Jan to Aug
Then column will be shown like
Jan Feb march April March - - - - - - - August

If user will select month from jan to Sept
Then column will be display as
Jan Feb March April March - - - - - - September

quote:
Originally posted by visakh16

make use of pivot

SELECT Class,Jan,Feb,Mar,Apr
FROM table t
PIVOT (MAX(heldornot) FOR [Month] IN (Jan,Feb,Mar,Apr))p

Thanks and Regards
Gyana Ranjan Ojha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 02:50:12

then use the second solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources