Pivoting Table: Generating Report ASP .net

USE [dbRegistration]
GO
/****** Object:  StoredProcedure [dbo].[sp_StudentGradeShow]    Script Date: 11/16/2011 09:49:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_StudentGradeShow]
as
--exec sp_StudentGradeShow
begin
       DECLARE @Cols NVARCHAR(2000)
       SET @Cols = ''
       --collect distinct Course Code that we have in tblStudentResult
       SELECT @Cols = @Cols + '[' + CourseCode + ']' + ', ' FROM
       (SELECT DISTINCT isnull(CourseCode,'N/A') as CourseCode FROM
		vw_TabulationSheetDeptTermWise ) AS s order by s.CourseCode

       --remove last comma
       SET @Cols = LEFT(@Cols,LEN(@Cols)-1)
       SET @Cols = 'SELECT * FROM (SELECT StudentId, CourseCode,LetterGrade FROM
		vw_TabulationSheetDeptTermWise ) ps PIVOT ( MAX(LetterGrade) FOR CourseCode IN (
		'+@cols+') ) AS pvt'
       EXECUTE sp_executeSQL @Cols
end

Output
StuentId      CSE1101  CSE1102 CSE1107
1107551	        A+      A-      NULL
200207013	A	NULL	NULL
201107030	NULL	NULL	NULL
201107551	NULL	NULL	NULL
Advertisements

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