declare @day int set @day = 2 declare @days varchar(8000) set @days = '[1]' -- Declare final column names for Pivot Declare @columnsday NVARCHAR(MAX) set @columnsday = 'isnull(cast([1] as varchar(10)),'''') as [1]' WHILE (@day <= 31) BEGIN set @days = @days + ', ' + quotename(convert(varchar, @day)) set @columnsday = @columnsday + ',ISNULL(cast(' + quotename(convert(varchar, @day)) + ' as varchar(10)) ,'''') as '+ quotename(convert(varchar, @day)) set @day = @day + 1 --print @days END --print @days --print @columnsday DECLARE @query varchar(8000) set @query = 'SELECT StudentCurrId,StudentCode,Stuname,' + @columnsday + 'from (SELECT tbl_StudentAttendanceDetails.StuAttendId,tbl_StudentAttendanceDetails.StudentCurrId, StudentCode,upper(First_Name + '' '' + Middle_Name + '' '' + Last_Name) as Stuname, day(StudentAttendanceDate) as day,Stuattend FROM tbl_StudentAttendanceDetails inner join tbl_StudentAttendanceMaster on tbl_StudentAttendanceDetails.StuAttendId=tbl_StudentAttendanceMaster.StuAttendId inner join tbl_Student_Current on tbl_StudentAttendanceDetails.StudentCurrId=tbl_Student_Current.StuCurrentId inner join StudentAdmission on tbl_Student_Current.StudentId=StudentAdmission.StudentId where tbl_StudentAttendanceMaster.SessionId=18'++' and tbl_StudentAttendanceMaster.ClassId=54'++' and tbl_StudentAttendanceMaster.StreamId=0'++' ) AS SourceTable pivot ( MAX(Stuattend) for day in (' + @days +') )p' print('pivoted report') exec(@query)
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)