title: SUM of dynamic Columns in PIVOT table in SQL Server
body: <p>I have a Dynamic PIVOT query in which Columns are dynamically generated.</p>
  
  <p>My table:  ATTENDANCE_MASTER
  Contains:  ID, Stud_id, ATT_DATE, PRESENT</p>
  
  <p>which stores data like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have created PIVOT Query</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I need sum of columns like</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Please suggest me solution.</p>
  
  <p>Thanks in Advance.</p>