Dynamic Pivot Table With Column And Row Totals In Sql Server 2012

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

Solution 1:

Try like this

Schema from your question:

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

INSERTINTO #RPT_DailySalesSummary
SELECT'2016-12-01', 'R101',    100,          81UNIONALLSELECT'2016-12-01', 'R102',    120,          81UNIONALLSELECT'2016-12-01', 'R113',    150,          82UNIONALLSELECT'2016-12-01', 'R104',    130 ,         85UNIONALLSELECT'2016-12-02', 'R205',    250 ,         81UNIONALLSELECT'2016-12-02', 'R106',    104,          82UNIONALLSELECT'2016-12-02', 'R112',    80 ,          85UNIONALLSELECT'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 PivotSELECT@cols=@cols+ QUOTENAME(LocRecID) +','FROM (SELECTDISTINCT LocRecID FROM #RPT_DailySalesSummary ) AS tmp
SELECT@cols=SUBSTRING(@cols, 0, LEN(@cols))

--Preparing sum of columns for Totals HorizontalSELECT@COLS_SUM =@COLS_SUM + QUOTENAME(LocRecID) +'+'FROM (SELECTDISTINCT 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 VerticallySELECT@COLS_TOT =@COLS_TOT +'SUM('+ QUOTENAME(LocRecID) +'),'FROM (SELECTDISTINCT 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║╚════════════╩═════╩═════╩═════╩═══════╝