SQL Server 2012 中具有列和行总计的动态数据透视表 [英] Dynamic pivot table with column and row totals in SQL Server 2012
本文介绍了SQL Server 2012 中具有列和行总计的动态数据透视表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有包含 CalDate、OrderID、SalesAmount、LocRecID 列的 RPT_DailySalesSummary 表.
<前>CalDate OrderID SalesAmount LocRecID2016-12-01 R101 100 812016-12-01 R102 120 812016-12-01 R113 150 822016-12-01 R104 130 852016-12-02 R205 250 812016-12-02 R106 104 822016-12-02 R112 80 852016-12-02 R032 80 85我想在每个日历日期按位置 ID 输出总和的结果表.(注:位置数量是动态的)
<前>CalDate 81 82 85 总计2016-12-01 220 150 130 5002016-12-02 250 104 160 514总计 470 254 290 1014我在下面写的代码可以输出数据透视表但没有行和列总数.
DECLARE @cols NVARCHAR(MAX)=''声明 @query NVARCHAR(MAX)=''SELECT @cols = @cols + QUOTENAME(LocRecID) + ','FROM (SELECT DISTINCT LocRecID FROM dbo.RPT_DailySalesSummary) AS tmpSELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))设置@查询='选择*从(选择 CalDate、SalesAmount、LocRecIDFROM dbo.RPT_DailySalesSummary) 源文件枢(SUM(SalesAmount) FOR LocRecID IN (' + @cols + ')) piv'执行(@查询)我觉得很困难，并没有真正理解如何使用 PIVOT.我不知道如何继续代码来创建我的预期结果.
有人可以帮忙吗?非常感谢.
解决方案
试试这个
来自您问题的架构:
创建表 #RPT_DailySalesSummary (CalDate 日期,订单ID VARCHAR(10),销售额 INT,LocRecID INT)插入 #RPT_DailySalesSummary选择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并且您需要像准备列列表一样准备列的 SUM
DECLARE @cols NVARCHAR(MAX)=''声明 @query NVARCHAR(MAX)=''声明 @COLS_SUM NVARCHAR(MAX)=''声明 @COLS_TOT NVARCHAR(MAX)=''-- 为 Pivot 准备列SELECT @cols = @cols + QUOTENAME(LocRecID) + ','FROM (SELECT DISTINCT LocRecID FROM #RPT_DailySalesSummary) AS tmpSELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))-- 为 Totals Horizontal 准备列的总和SELECT @COLS_SUM = @COLS_SUM + QUOTENAME(LocRecID) + '+'FROM (SELECT DISTINCT LocRecID FROM #RPT_DailySalesSummary) AS tmpSELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +'总计'-- 垂直准备总计的各个列的总和SELECT @COLS_TOT = @COLS_TOT +'SUM('+ QUOTENAME(LocRecID) + '),'FROM (SELECT DISTINCT LocRecID FROM #RPT_DailySalesSummary) AS tmpSELECT @COLS_TOT = SUBSTRING(@COLS_TOT, 0, LEN(@COLS_TOT))设置@查询='SELECT *'+@COLS_SUM+' INTO #TAB FROM(选择 CalDate、SalesAmount、LocRecID来自#RPT_DailySalesSummary) 源文件枢(SUM(SalesAmount) FOR LocRecID IN (' + @cols + ')) piv选择 * 从 #TAB联合所有SELECT NULL AS TOTAL ,'+@COLS_TOT+',SUM(TOTAL) FROM #TAB'执行(@查询)结果是
╔============╦=====╦=====╦====╦======╗║ 校准日期 ║ 81 ║ 82 ║ 85 ║ 总计 ║╠===========╬=====╬=====╬=====╬=======╣║ 2016-12-01 ║ 220 ║ 150 ║ 130 ║ 500 ║║ 2016-12-02 ║ 250 ║ 104 ║ 160 ║ 514 ║║ NULL ║ 470 ║ 254 ║ 290 ║ 1014 ║╚===========╩=====╩=====╩=====╩=======╝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.
解决方案
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 ║
╚════════════╩═════╩═════╩═════╩═══════╝
这篇关于SQL Server 2012 中具有列和行总计的动态数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
