krishna pandey

krishna pandey

  • 909
  • 596
  • 20k

this store proceser how can bind gridview

Dec 19 2014 11:59 PM
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

) AS SourceTable

pivot (
MAX(Stuattend)
for day in (' + @days +')
)p'

print('pivoted report')
exec(@query)