SQL Server 2012 中具有列和行总计的动态数据透视表

我有表 RPT_DailySalesSummary，其中包含 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
  

我想输出下面的结果表，按每个日历日期的位置 ID 求和。 （注：地点数量是动态的）

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

我写的下面的代码可以输出数据透视表，但没有行和列总计。

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)

我感觉很难，不太明白如何使用 PIVOT。我不知道如何继续代码来创建我的预期结果。

有人可以帮忙吗？非常感谢。

尝试这样

你的问题的架构：

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

您需要像准备列列表一样准备列的 SUM

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)

结果将是

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