ROLLUP, CUBE or GROUPING SETS

Question

Please refer to the link to http://sqlfiddle.com/#!3/1b16f/7 for the schema details. I have used dynamic pivot query to get the month on month details. Now, I am trying to get the sub total for "transactional" and "Customer" and the grand total at the end on the same pivot query. So far I have tried grouping with cubes and rollup but did not get a satisfactory result.

I am trying to achieve this at the end.

+---------------------+---------------+-------------+-------------+-------------+
|         CTQ         |     TYPE      |   Oct-12    |   Nov-12    |   Dec-12    |
+---------------------+---------------+-------------+-------------+-------------+
| CAR                 | CUSTOMER      | 4.019040077 | 4.128151178 | 4.05606883  |
| REJECTION           | CUSTOMER      | NULL        | NULL        | 3.126549759 |
| REOPEN              | CUSTOMER      | 3.010499668 | 3.043744049 | 2.985404802 |
| REPEAT              | CUSTOMER      | 3.086827141 | 3.055288995 | 3.202606293 |
| CUSTOMER TOTAL      |               | 2.111       | 2.111       | 2.111       |
| QMC                 | TRANSACTIONAL | 3.699047224 | 3.417352407 | 3.525099552 |
| SQ                  | TRANSACTIONAL | NULL        | 5.017541394 | 4.90992542  |
| TAT                 | TRANSACTIONAL | 2.548737677 | 2.509322914 | 2.538090124 |
| TTU                 | TRANSACTIONAL | 3.364436242 | 3.295240684 | 3.294951974 |
| TRANSACTIONAL TOTAL |               | 2.33        | 2.33        | 2.33        |
| GRAND TOTAL         |               | 2.12        | 2.12        | 2.12        |
+---------------------+---------------+-------------+-------------+-------------+

I am using SQL Server 2008R2. Please help!!

Answer 1

I was able to answer my question with this query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

  SELECT @cols = ISNULL(@cols+',','') + QUOTENAME(DATENAME(MONTH, MNTH)+' '+DATENAME(YEAR, MNTH)) 
    FROM CAPABILITY
   WHERE  MNTH BETWEEN DATEADD(M,-10,CURRENT_TIMESTAMP) AND DATEADD(M,0,CURRENT_TIMESTAMP)
    GROUP BY DATENAME(MONTH, MNTH), DATENAME(YEAR, MNTH), DATEPART(MONTH,mnth)
    ORDER BY DATENAME(YEAR, MNTH), DATEPART(MONTH,mnth)
SET @query = 'SELECT  TYPE,CTQ,' 

+@cols+ 
'FROM  

(
SELECT 
CASE 
WHEN CTQ IS NULL AND TYPE IS NULL THEN ''CUST + TRANS TOTAL''
WHEN CTQ IS NULL AND TYPE IS NOT NULL THEN TYPE+''TOTAL'' 
WHEN CTQ IS NULL AND TYPE IS NULL THEN ''''
WHEN GROUPING(TYPE)=1 THEN ''CUST + TRANS''
ELSE TYPE
END AS [TYPE],
CTQ,        
    SUM(opportunity)AS Sigma    

    ,DATENAME(MONTH, MNTH)+'' ''+DATENAME(YEAR, MNTH)
 AS MONTHS
    FROM CAPABILITY

        GROUP BY ROLLUP(CTQ), ROLLUP(TYPE),DATENAME(MONTH, MNTH)+'' ''+DATENAME(YEAR, MNTH)

)X

PIVOT 
            (
                MIN(SIGMA)
                FOR MONTHS in (' + @cols + ')
            ) X 

        ORDER BY TYPE ASC
           '
    --PRINT (@QUERY)
EXECUTE (@QUERY)

See this SQL Fiddle for demonstration: http://sqlfiddle.com/#!3/1b16f/18