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.
I'd firstly recommend not using variable concatenation to create your column list. It's behaviour is undefined and can be unexpected. Instead use SQL Server's XML extensions:
SET @Columns = (SELECT N', p.' + QUOTENAME(p.Att_Date)
FROM dbo.ATTENDANCE_MASTER AS p
GROUP BY p.ATT_DATE
ORDER BY p.ATT_DATE
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');
Then you can simply use @Columns
to create an expression for the total, so using:
', Total = ' + STUFF(REPLACE(@columns, ', p.[', ' + p.['), 1, 3, '')
You get something like:
, Total = p.[2015-08-01] + p.[2015-08-02]
Which you can add to your dynamic SQL, so for a full working example:
IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL DROP TABLE #T;
CREATE TABLE #T ([ID] int, [Stud_id] int, [ATT_DATE] datetime, [PRESENT] int);
INSERT INTO #T ([ID], [Stud_id], [ATT_DATE], [PRESENT])
VALUES
(1, 1, '2015-08-01 00:00:00', 1),
(2, 2, '2015-08-01 00:00:00', 0),
(3, 3, '2015-08-01 00:00:00', 1),
(4, 1, '2015-08-02 00:00:00', 0),
(5, 2, '2015-08-02 00:00:00', 1),
(6, 3, '2015-08-02 00:00:00', 1);
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @Columns = (SELECT N', p.' + QUOTENAME(REPLACE(CONVERT(VARCHAR(10), p.Att_Date, 111), '/', '-'))
FROM #T AS p
GROUP BY p.ATT_DATE
ORDER BY p.ATT_DATE
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');
SET @sql = N'SELECT Stud_id, ' + STUFF(@columns, 1, 2, '') + ', Total = ' + STUFF(REPLACE(@columns, ', p.[', ' + p.['), 1, 3, '') + '
FROM
(
SELECT p.ATT_DATE, p.Stud_id, p.PRESENT FROM #T 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;