Dynamic pivot table with column and row totals in SQL Server 2012

Question

I have table RPT_DailySalesSummary with column CalDate, OrderID, SalesAmount, LocRecID.

CalDate    OrderID SalesAmount  LocRecID
2016-12-01 R101    100          81
2016-12-01 R102    120          81
2016-12-01 R113    150          82
2016-12-01 R104    130          85
2016-12-02 R205    250          81
2016-12-02 R106    104          82
2016-12-02 R112    80           85
2016-12-02 R032    80           85

I want to output below result table for sum by location id for each calendar date. (Notes: no. of locations is dynamic)

CalDate      81    82    85    Total
2016-12-01   220   150   130   500
2016-12-02   250   104   160   514
Total        470   254   290   1014

I wrote below code can output pivot table but no row and column totals.

DECLARE @cols       NVARCHAR(MAX)=''
DECLARE @query      NVARCHAR(MAX)=''

SELECT @cols = @cols + QUOTENAME(LocRecID) + ',' 
FROM (SELECT DISTINCT LocRecID FROM dbo.RPT_DailySalesSummary ) AS tmp
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))


SET @query = 
            'SELECT * FROM 
            (
                    SELECT CalDate, SalesAmount, LocRecID 
                    FROM dbo.RPT_DailySalesSummary
            ) src
            PIVOT 
            (
                    SUM(SalesAmount) FOR LocRecID IN (' + @cols + ')
            ) piv'

execute(@query)

I feel difficult and not real understand how to use PIVOT. I don't know how continue the code to create my expect result.

Anyone can help? Thank a lot.

Answer 1

Try like this

Schema from your question:

CREATE TABLE #RPT_DailySalesSummary (
    CalDate DATE
    ,OrderID VARCHAR(10)
    ,SalesAmount INT
    ,LocRecID INT
    )

INSERT INTO #RPT_DailySalesSummary
SELECT '2016-12-01', 'R101',    100,          81
UNION ALL
SELECT '2016-12-01', 'R102',    120,          81
UNION ALL
SELECT '2016-12-01', 'R113',    150,          82
UNION ALL
SELECT '2016-12-01', 'R104',    130 ,         85
UNION ALL
SELECT '2016-12-02', 'R205',    250 ,         81
UNION ALL
SELECT '2016-12-02', 'R106',    104,          82
UNION ALL
SELECT '2016-12-02', 'R112',    80 ,          85
UNION ALL
SELECT '2016-12-02', 'R032',    80 ,          85

And you need to prepare SUM of columns like you prepared column list

DECLARE @cols       NVARCHAR(MAX)=''
DECLARE @query      NVARCHAR(MAX)=''
DECLARE @COLS_SUM   NVARCHAR(MAX)=''
DECLARE @COLS_TOT   NVARCHAR(MAX)=''

--Preparing columns for Pivot
SELECT @cols = @cols + QUOTENAME(LocRecID) + ',' 
FROM (SELECT DISTINCT LocRecID FROM #RPT_DailySalesSummary ) AS tmp
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))

--Preparing sum of columns for Totals Horizontal
SELECT @COLS_SUM = @COLS_SUM + QUOTENAME(LocRecID) + '+' 
FROM (SELECT DISTINCT LocRecID FROM #RPT_DailySalesSummary ) AS tmp
SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +' AS TOTAL'

--Preparing sum of individual columns for Totals Vertically
SELECT @COLS_TOT = @COLS_TOT +'SUM('+ QUOTENAME(LocRecID) + '),' 
FROM (SELECT DISTINCT LocRecID FROM #RPT_DailySalesSummary ) AS tmp
SELECT @COLS_TOT = SUBSTRING(@COLS_TOT, 0, LEN(@COLS_TOT)) 



SET @query = 
            'SELECT *'+@COLS_SUM+'  INTO #TAB FROM 
            (
                    SELECT CalDate, SalesAmount, LocRecID 
                    FROM #RPT_DailySalesSummary
            ) src
            PIVOT 
            (
                    SUM(SalesAmount) FOR LocRecID IN (' + @cols + ')
            ) piv

            SELECT * FROM #TAB
            UNION ALL
            SELECT NULL AS TOTAL ,'+@COLS_TOT+',SUM(TOTAL) FROM #TAB

            '

execute(@query)

And the result will be

╔════════════╦═════╦═════╦═════╦═══════╗
║  CalDate   ║ 81  ║ 82  ║ 85  ║ TOTAL ║
╠════════════╬═════╬═════╬═════╬═══════╣
║ 2016-12-01 ║ 220 ║ 150 ║ 130 ║   500 ║
║ 2016-12-02 ║ 250 ║ 104 ║ 160 ║   514 ║
║ NULL       ║ 470 ║ 254 ║ 290 ║  1014 ║
╚════════════╩═════╩═════╩═════╩═══════╝