Subtotals and grand totals SQL Pivot

Question

Currently have a script that creates a pivot table with current year values subtraction prior year values.

use devmreports

-- Creates dynamic values for pivot table
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(month)
                    from ABR
                    group by ',' + QUOTENAME(month)
                    order by datalength(',' + QUOTENAME(month))
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- Pivot table for YOY change in booked passengers
set @query
=

'SELECT     Region, 
            CityPair, 
            Year, 
            ' + @cols + '


FROM         
(
    SELECT    ABR.Region, 
                ABR.CityPair, 
                ABR.Year, 
                ABR.Month,
                ABR.Adv_B - ABRP.Adv_B as Total
    FROM    ABR LEFT OUTER JOIN
                      ABRP ON  
                      ABR.Month = ABRP.Month AND 
                      ABR.CityPair = ABRP.CityPair) P
                        PIVOT
                        (
                        SUM(Total) 
                        FOR MONTH IN 
                        (
                        '+@cols+'))as pvt'


execute (@Query)

Current Pivot looks like this:

+------------+----------+----+-----+-----+----+
|     Region | CityPair | 8  |  9  | 10  | 11 |
+------------+----------+----+-----+-----+----+
|     A      |        1 | 16 |  17 |  18 |  7 |
|     A      |        2 | 17 | -20 | -10 |  1 |
|     B      |        3 |  5 |   8 |   4 | -3 |
|     B      |        4 | 21 |  10 |   3 |  2 |
|     C      |        5 | 15 | -14 | -12 |  1 |
+------------+----------+----+-----+-----+----+

What I would like to have is this:

+-----------------+----------+----+-----+-----+----+
|       Region    | CityPair | 8  |  9  | 10  | 11 |
+-----------------+----------+----+-----+-----+----+
|     A           |        1 | 16 |  17 |  18 |  7 |
|     A           |        2 | 17 | -20 | -10 |  1 |
|     A Total     |          | 33 |  -3 |   8 |  8 |
|     B           |        3 |  5 |   8 |   4 | -3 |
|     B           |        4 | 21 |  10 |   3 |  2 |
|     B Total     |          | 26 |  18 |   7 | -1 |
|     C           |        5 | 15 | -14 | -12 |  1 |
|     C Total     |          | 15 | -14 | -12 |  1 |
|     Grand Total |          | 74 |   1 |   3 |  8 |
+-----------------+----------+----+-----+-----+----+

Any assistance would be greatly appreciated.

Answer 1

My suggestion would be to look at using GROUP BY ROLLUP to get the total rows.

The basic syntax if you were hard-coding the query would be:

select 
  case 
    when region is null then 'Grand Total' 
    when citypair is null then region +' Total' 
    else region end region,
  coalesce(cast(citypair as varchar(10)), '') citypair,
  sum([8]) [8], 
  sum([9]) [9]
from
(
  select region, citypair, month, total
  from yourtable
) d
pivot
(
  sum(total)
  for month in ([8], [9])
) piv
GROUP BY rollup(region, citypair);

See SQL Fiddle with Demo. Then to use your dynamic SQL version you could alter the code to use:

-- Creates dynamic values for pivot table
DECLARE @cols AS NVARCHAR(MAX),
    @colsRollup AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(month)
                    from ABR
                    group by ',' + QUOTENAME(month)
                    order by datalength(',' + QUOTENAME(month))
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsRollup = STUFF((SELECT ', sum(' + QUOTENAME(month)+ ') as '+ QUOTENAME(month)
                    from ABR
                    group by ',' + QUOTENAME(month)
                    order by datalength(',' + QUOTENAME(month))
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- Pivot table for YOY change in booked passengers
set @query
=

'SELECT     
      case 
        when region is null then ''Grand Total'' 
        when citypair is null then region +'' Total' '
        else region end region,
      coalesce(cast(citypair as varchar(10)), '''') citypair, 
            ' + @colsRollup + '


FROM         
(
    SELECT    ABR.Region, 
                ABR.CityPair, 
                ABR.Year, 
                ABR.Month,
                ABR.Adv_B - ABRP.Adv_B as Total
    FROM    ABR LEFT OUTER JOIN
                      ABRP ON  
                      ABR.Month = ABRP.Month AND 
                      ABR.CityPair = ABRP.CityPair
) P
PIVOT
(
  SUM(Total) 
  FOR MONTH IN ('+@cols+')
)as pvt
GROUP BY rollup(region, citypair);'


execute sp_executesql @Query