title: SQL Dynamic Pivot Query With Multiple Subtotal
body: <p>For below code, I able to create a dynamic pivot with subtotal.But I need to add one more subtotal column in this code . It has to be total of <strong>NUM_TO_EXIT</strong> values.As you can see picture 2, I used <strong>NUM_TO_ENTER</strong> within dynamic dates in my dynamic pivot but I could not able to merge just for the total values of <strong>NUM_TO_EXIT</strong>. I tried everything but could not able to do that. Appreciate for yor supports.I am using MSSQL 2008 R2.</p>
  
  <pre><code>  DIRECTIONAL_METRIC_ID ZONE_ID START_TIME  END_TIME    RECEIVED_TS STATUS  NUM_TO_ENTER    NUM_TO_EXIT DAY_OF_WEEK_ID  TIME_ID
  436909  254 2016-02-06 10:00:00.000 2016-02-06 10:00:00.000 2016-02-06 10:00:00.000 1   195 195 7   300
  436910  254 2016-02-07 10:00:00.000 2016-02-07 10:15:00.000 2016-02-07 10:15:00.000 1   195 195 7   300
  436911  278 2016-02-01 10:00:00.000 2016-02-01 10:15:00.000 2016-02-01 10:15:00.000 1   95  95  7   300
  436912  278 2016-02-02 10:00:00.000 2016-02-02 10:15:00.000 2016-02-02 10:15:00.000 1   95  95  7   300
  436913  278 2016-02-03 10:00:00.000 2016-02-03 10:15:00.000 2016-02-03 10:15:00.000 1   95  95  7   300
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/dd4i4.png" rel="nofollow noreferrer">What I need</a></p>
  
  <p><a href="https://i.stack.imgur.com/vHstW.png" rel="nofollow noreferrer">Structe of DIRECTIONAL_METRIC TABLE</a></p>
  
  <pre><code>DECLARE @mydate DATETIME
  SELECT @mydate = GETDATE()
  
  IF OBJECT_ID('tempdb..#Dates') IS NOT NULL
  drop table #Dates
  
  
  SELECT DISTINCT CONVERT(VARCHAR,START_TIME,106) AS [Date]
  INTO #Dates
  FROM dbo.DIRECTIONAL_METRIC
  WHERE CAST(START_TIME as DATE) &gt;=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
  and CAST(START_TIME as DATE) &lt; @mydate
  AND DATEPART(HOUR,START_TIME) BETWEEN 9 AND 23
  order by [Date]
  
  DECLARE @cols VARCHAR(1000)
  SELECT @cols = COALESCE (@cols + ',[' + [Date] +']','[' + [Date] + ']')
  FROM #Dates
  
  
  DECLARE @GrandTotalCol  NVARCHAR (MAX)
  SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + [Date] +'],0) + ', 'ISNULL([' + [Date]+ '],0) + ')
  FROM #Dates 
  ORDER BY [Date]
  SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)
  
  DECLARE @qry varchar(4000)
  SET @qry =
  
  '
  DECLARE @mydate DATETIME
  SELECT @mydate = GETDATE()
  
  SELECT *, ('+ @GrandTotalCol + ') 
  AS [Grand Total] INTO #temp_MatchesTotal 
  FROM 
  (
          SELECT ZO.CODE AS KOD,
          convert(varchar, START_TIME, 106) AS [Date],
          SUM(NUM_TO_ENTER) AS NUM_TO_ENTER
          FROM  dbo.DIRECTIONAL_METRIC AS z INNER JOIN ZONE AS ZO ON z.ZONE_ID=ZO.ZONE_ID
          WHERE CAST(START_TIME as DATE) &gt;=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
          and CAST(START_TIME as DATE) &lt; @mydate
          and DATEPART(HOUR,START_TIME) between 9 and 23
          AND ZO.CODE IS NOT NULL
          group by ZO.CODE,convert(varchar, START_TIME, 106)
  ) P
  PIVOT
  ( 
           SUM(NUM_TO_ENTER) 
           FOR  [Date] IN(' +@cols + ')
  )
   AS pvt
  ORDER BY KOD
  SELECT * FROM #temp_MatchesTotal 
  DROP TABLE #temp_MatchesTotal'
  
  
  EXEC(@qry)
  GO
  </code></pre>