I have a Dynamic PIVOT query in which Columns are dynamically generated.
My table: ATTENDANCE_MASTER Contains: ID, Stud_id, ATT_DATE, PRESENT
which stores data like:
ID Stud_id ATT_DATE PRESENT
1 1 2015-08-1 1
2 2 2015-08-1 0
3 3 2015-08-1 1
4 1 2015-08-2 0
5 2 2015-08-2 1
6 3 2015-08-2 1
I have created PIVOT Query
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(ATT_DATE)
FROM (SELECT p.ATT_DATE FROM dbo.ATTENDANCE_MASTER AS p
GROUP BY p.ATT_DATE) AS x;
SET @sql = N'SELECT Stud_id, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT p.ATT_DATE, p.Stud_id, p.PRESENT FROM dbo.ATTENDANCE_MASTER AS p
) AS j
PIVOT
(
SUM(PRESENT) FOR ATT_DATE IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
I need sum of columns like
Stud_ID 2015-08-01 2015-08-2 2015-08-3 Total
1 1 0 1 2
2 1 1 1 3
3 1 1 0 2
4 0 0 1 1
Please suggest me solution.
Thanks in Advance.