动态透视中的行和列总计 [英] 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
ROLLUP
instead ofCUBE
.
2. Show only Column Total
- Use the code
SELECT @cols += ',[Total]'
andSELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
. - Use
ROLLUP
instead ofCUBE
. - Change
GROUP BY Location,PartCode
toGROUP 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屋!