动态透视中的行和列总计 [英] Row and column total in dynamic pivot
本文介绍了动态透视中的行和列总计的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在 SQL Server 2008 中，我有一个包含 3 列的表 (tblStock):
<块引用>- 零件代码 (NVARCHAR (50))
- 库存数量 (INT)
- 位置 (NVARCHAR(50))
下面的一些示例数据:
PartCode StockQty Location……………………A 10 WHs-AB 22 WHs-AA 1 WHs-BC 20 WHs-AD 39 WHs-FE 3 WHs-DF 7 WHs-AA 9 WHs-CD 2 WHs-AF 54 WHs-E如何创建程序以获得如下结果?
PartCode WHs-A WHs-B WHs-C WHs-D WHs-E WHs-F 合计……………………………………………………10 1 9 0 0 0 20B 22 0 0 0 0 0 22C 20 0 0 0 0 0 20D 2 0 0 0 0 39 41E 0 0 0 3 0 0 3F 7 0 0 0 54 0 61总计 61 1 9 3 54 39 167非常感谢您的帮助，谢谢.
解决方案
SAMPLE TABLE
SELECT * INTO #tblStock从(选择A"零件代码、10 个库存数量、WHs-A"位置联合所有选择B"，22，WHs-A"联合所有选择'A'，1，'WHs-B'联合所有选择'C'，20，'WHs-A'联合所有选择'D'，39，'WHs-F'联合所有选择'E'，3，'WHs-D'联合所有选择'F'，7，'WHs-A'联合所有选择'A'，9，'WHs-C'联合所有选择D"，2，WHs-A"联合所有选择'F'，54，'WHs-E')标签获取动态旋转的列并将 NULL 替换为 zero
DECLARE @cols NVARCHAR (MAX)SELECT @cols = COALESCE (@cols + ',[' + Location + ']', '[' + Location + ']')FROM(从#tblStock 中选择不同的位置)PV按位置订购-- 因为最后一列需要 Total，所以最后追加SELECT @cols += ',[总计]'--用零替换NULL的变量声明 @NulltoZeroCols NVARCHAR (MAX)SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+Location+'],0) AS ['+Location+']'FROM(从#tblStock 选择不同的位置)选项卡按位置排序 XML PATH('')),2,8000)SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'您可以使用 CUBE 查找行和列总计，并将 NULL 替换为 Total 用于从 CUBE.
DECLARE @query NVARCHAR(MAX)SET @query = 'SELECT PartCode,' + @NulltoZeroCols + ' FROM(选择ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode,总和(库存数量)库存数量，ISNULL(位置,''总计'')位置来自#tblStock按位置、零件代码分组带立方体) X枢(最小(库存数量)FOR 位置 IN (' + @cols + ')) pORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode'执行 SP_EXECUTESQL @query- 查看结果
In SQL Server 2008, I have a table (tblStock) with 3 columns:
- PartCode (NVARCHAR (50))
- StockQty (INT)
- Location (NVARCHAR(50))
some example data below:
PartCode StockQty Location
......... ......... .........
A 10 WHs-A
B 22 WHs-A
A 1 WHs-B
C 20 WHs-A
D 39 WHs-F
E 3 WHs-D
F 7 WHs-A
A 9 WHs-C
D 2 WHs-A
F 54 WHs-E
How to create procedure to get the result as below?
PartCode WHs-A WHs-B WHs-C WHs-D WHs-E WHs-F Total
........ ..... ..... ..... ...... ..... ..... .....
A 10 1 9 0 0 0 20
B 22 0 0 0 0 0 22
C 20 0 0 0 0 0 20
D 2 0 0 0 0 39 41
E 0 0 0 3 0 0 3
F 7 0 0 0 54 0 61
Total 61 1 9 3 54 39 167
Your help is much appreciated, thanks.
解决方案
SAMPLE TABLE
SELECT * INTO #tblStock
FROM
(
SELECT 'A' PartCode, 10 StockQty, 'WHs-A' Location
UNION ALL
SELECT 'B', 22, 'WHs-A'
UNION ALL
SELECT 'A', 1, 'WHs-B'
UNION ALL
SELECT 'C', 20, 'WHs-A'
UNION ALL
SELECT 'D', 39, 'WHs-F'
UNION ALL
SELECT 'E', 3, 'WHs-D'
UNION ALL
SELECT 'F', 7, 'WHs-A'
UNION ALL
SELECT 'A', 9, 'WHs-C'
UNION ALL
SELECT 'D', 2, 'WHs-A'
UNION ALL
SELECT 'F', 54, 'WHs-E'
)TAB
Get the columns for dynamic pivoting and replace NULL with zero
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + Location + ']', '[' + Location + ']')
FROM (SELECT DISTINCT Location FROM #tblStock) PV
ORDER BY Location
-- Since we need Total in last column, we append it at last
SELECT @cols += ',[Total]'
--Varible to replace NULL with zero
DECLARE @NulltoZeroCols NVARCHAR (MAX)
SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+Location+'],0) AS ['+Location+']'
FROM (SELECT DISTINCT Location FROM #tblStock)TAB
ORDER BY Location FOR XML PATH('')),2,8000)
SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
You can use CUBE to find row and column total and replace NULL with Total for the rows generated from CUBE.
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT PartCode,' + @NulltoZeroCols + ' FROM
(
SELECT
ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode,
SUM(StockQty)StockQty ,
ISNULL(Location,''Total'')Location
FROM #tblStock
GROUP BY Location,PartCode
WITH CUBE
) x
PIVOT
(
MIN(StockQty)
FOR Location IN (' + @cols + ')
) p
ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode'
EXEC SP_EXECUTESQL @query
- Click here to view result
RESULT
NOTE : If you want NULL instead of zero as values, use @cols instead of @NulltoZeroCols in dynamic pivot code
EDIT :
1. Show only Row Total
- Do not use the code
SELECT @cols += ',[Total]'andSELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'. - Use
ROLLUPinstead ofCUBE.
2. Show only Column Total
- Use the code
SELECT @cols += ',[Total]'andSELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'. - Use
ROLLUPinstead ofCUBE. - Change
GROUP BY Location,PartCodetoGROUP BY PartCode,Location. - Instead of
ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode, useWHERE PartCode<>''TOTAL'' ORDER BY PartCode.
UPDATE : To bring PartName for OP
I am updating the below query to add PartName with result. Since PartName will add extra results with CUBE and to avoid confusion in AND or OR conditions, its better to join the pivoted result with the DISTINCT values in your source table.
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT P.PartCode,T.PartName,' + @NulltoZeroCols + ' FROM
(
SELECT
ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode,
SUM(StockQty)StockQty ,
ISNULL(Location,''Total'')Location
FROM #tblStock
GROUP BY Location,PartCode
WITH CUBE
) x
PIVOT
(
MIN(StockQty)
FOR Location IN (' + @cols + ')
) p
LEFT JOIN
(
SELECT DISTINCT PartCode,PartName
FROM #tblStock
)T
ON P.PartCode=T.PartCode
ORDER BY CASE WHEN (P.PartCode=''Total'') THEN 1 ELSE 0 END,P.PartCode'
EXEC SP_EXECUTESQL @query
- Click here to view result
这篇关于动态透视中的行和列总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
