Pivot Report: Dynamic header

USE [dbRegistration]
GO
/****** Object:  StoredProcedure [dbo].[sp_TabulationSheetDeptTermWise]    Script Date: 11/16/2011 11:28:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_TabulationSheetDeptTermWise]
as
--exec sp_TabulationSheetDeptTermWise
begin
	DECLARE	@Cols NVARCHAR(2000),
			@Parameter NVARCHAR(2000),
			@StudentId int,
			@StudentName varchar(100),
			@DeptId varchar(50),
			@CourseCode1 varchar(50),
			@CourseCode2 varchar(50),
			@CourseCode3 varchar(50),
			@CourseCode4 varchar(50),
			@CourseCode5 varchar(50),
			@CourseCode6 varchar(50),
			@CourseCode7 varchar(50),
			@CourseCode8 varchar(50),
			@CourseCode9 varchar(50),
			@CourseCode10 varchar(50),
			@CourseCode11 varchar(50),
			@CourseCode12 varchar(50),
			@LetterGrade varchar(5),
			@IDX int,
			@CountId int,
			@Cols1 NVARCHAR(2000),
			@HeaderShow NVARCHAR(2000)

			set @IDX= 1
			set @CountId = 0
			SET @Cols = ''
			set @HeaderShow = ''
			set @Parameter = '@Cols NVARCHAR(2000)'
					 
			--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
			SET @Cols = LEFT(@Cols,LEN(@Cols)-1)
			
			SELECT @HeaderShow = @HeaderShow +  CourseCode +  ', ' FROM
			(SELECT DISTINCT isnull(CourseCode,'N/A') as CourseCode FROM
			vw_TabulationSheetDeptTermWise ) AS s order by s.CourseCode
			SET @HeaderShow = LEFT(@HeaderShow,LEN(@HeaderShow)-1)
			
			set @Cols1 = @Cols
			
			WHILE @IDX!= 0
			BEGIN
				SET @IDX = CHARINDEX(',',@Cols1)
				IF @IDX != 0
					SET @CountId = @CountId + 1
				ELSE
					SET @CountId = @CountId + 1

				SET @Cols1 = RIGHT(@Cols1,LEN(@Cols1)-@IDX)
				IF LEN(@Cols1)= 0 
					BREAK
			END
			
			--select @CountId
			if(@CountId = 1)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','+ ','+ ','+ ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'+ ',[........]'+ ',[.........]'+ ',[..........]'+ ',[...........]'					
				end
			else if(@CountId = 2)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','+ ','+ ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'+ ',[........]'+ ',[.........]'+ ',[..........]'					
				end
			else if(@CountId = 3)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','+ ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'+ ',[........]'+ ',[.........]'					
				end
			else if(@CountId = 4)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'+ ',[........]'					
				end			
			else if(@CountId = 5)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ','+ ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]'+ ',[.......]'					
				end
			else if(@CountId = 6)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ',' + ',' 
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]' + ',[......]' 				
				end		
			
			else if(@CountId = 7)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','+  ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'+ ',[.....]'	
				end				
			else if(@CountId = 8)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ',' + ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' + ',[....]'				
				end				
			else if(@CountId = 9)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' +  ','
					set @Cols = @Cols + ',[.]' + ',[..]' + ',[...]' 				
				end	
			else if(@CountId = 10)
				begin
					set @HeaderShow = @HeaderShow + ',' + ',' 
					set @Cols = @Cols + ',[.]' + ',[..]' 			
				end	
			else if(@CountId = 11)
				begin
					set @HeaderShow = @HeaderShow + ',' 
					set @Cols = @Cols + ',[.]' 			
				end	
			else if(@CountId = 12)
				begin
					set @HeaderShow = @HeaderShow  
					set @Cols = @Cols				
				end	
			
			
			
			select @HeaderShow as MainRow
		select @Cols	
			delete from T_TempReport
			declare @sqlstatement nvarchar(4000)
			--move declare cursor into sql to be executed
			set @sqlstatement = 'declare cursorName cursor for SELECT * FROM (SELECT StudentId,LetterGrade, CourseCode FROM
			vw_TabulationSheetDeptTermWise ) ps PIVOT ( MAX(LetterGrade) FOR CourseCode IN (
			'+@cols+') ) AS pvt'

			exec sp_executesql @sqlstatement,@Parameter ,@cols
			   
			open cursorName
			fetch next from cursorName
			into @StudentId,@CourseCode1,@CourseCode2 ,@CourseCode3,@CourseCode4,@CourseCode5,@CourseCode6,@CourseCode7,@CourseCode8,@CourseCode9,@CourseCode10,@CourseCode11,@CourseCode12
			while @@FETCH_STATUS = 0
			begin
					insert into T_TempReport(StudentId,Sub1,Sub2,Sub3,Sub4,Sub5,Sub6,Sub7,Sub8,Sub9,Sub10,Sub11,Sub12)
					values(@StudentId,@CourseCode1,@CourseCode2,@CourseCode3,@CourseCode4,@CourseCode5,@CourseCode6,@CourseCode7,@CourseCode8,@CourseCode9,@CourseCode10,@CourseCode11,@CourseCode12)
			
				fetch next from cursorName
				into @StudentId,@CourseCode1,@CourseCode2 ,@CourseCode3,@CourseCode4,@CourseCode5,@CourseCode6,@CourseCode7,@CourseCode8,@CourseCode9,@CourseCode10,@CourseCode11,@CourseCode12
			end
			close cursorName
			deallocate cursorName
      
     select Distinct CAST( T_TempReport.StudentId as Varchar(20)) as StudentId,T_Std_General_Information.StudentsName,T_TempReport.Sub1,T_TempReport.Sub2,T_TempReport.Sub3,T_TempReport.Sub4,T_TempReport.Sub5,T_TempReport.Sub6,
     T_TempReport.Sub7,T_TempReport.Sub8,T_TempReport.Sub9,T_TempReport.Sub10,T_TempReport.Sub11,T_TempReport.Sub12 
     from T_TempReport INNER JOIN T_Std_General_Information ON T_TempReport.StudentId=T_Std_General_Information.StudentId
end
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