Creating reports and dashboards | Stimulsoft community forum

Code: Select all

CREATE TABLE #yt 
(
  [PupilID] int, 
  [WeekNo] int, 
  [IsInAttendance] int
);

INSERT INTO #yt
(
  [PupilID], 
  [WeekNo],
  [IsInAttendance]
)
VALUES
    (102, 1, 1),
    (102, 1, 1),
    (102, 1, 1),
    (102, 1, 1),
    (102, 1, 0),
    (102, 1, 1),
    (102, 1, 0),
    (102, 1, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 1),
    (102, 2, 0),
    (102, 3, 1),
    (101, 3, 0),
    (101, 1, 1),
    (101, 1, 1),
    (101, 1, 1),
    (101, 1, 1),
    (101, 1, 1),
    (101, 6, 1),
    (101, 2, 1),
    (101, 4, 1),
    (101, 2, 1),
    (101, 3, 1),
    (101, 2, 0)

DECLARE @WkCols AS NVARCHAR(MAX),
        @WkQuery AS NVARCHAR(MAX)

select @WkCols = STUFF((SELECT ',' + QUOTENAME(WeekNo) 
                    FROM #yt
                    GROUP BY WeekNo
                    ORDER BY WeekNo
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @WkQuery = 'SELECT PupilID,' + @WkCols + ' FROM 
            (
                SELECT
                    T.PupilID,
                    T.WeekNo,
                    AttendancePercentage = SUM(T.IsInAttendance) * 100.0 / COUNT(1)
                FROM
                    #yt AS T
                GROUP BY
                    T.PupilID,
                    T.WeekNo
            ) x
            PIVOT 
            (
                MAX(AttendancePercentage)
                FOR WeekNo IN (' + @WkCols + ')
            ) p '

execute(@WkQuery);