动态枢轴中的行和列总计 [英] Row and column total in dynamic pivot

问题描述

在SQL Server 2008中，我有一个包含三列的表(tblStock):

In SQL Server 2008, I have a table (tblStock) with 3 columns:

• PartCode(NVARCHAR(50))
• 库存数量(INT)
• 位置(NVARCHAR(50))

• PartCode (NVARCHAR (50))
• StockQty (INT)
• Location (NVARCHAR(50))

下面的一些示例数据:

    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

非常感谢您的帮助.

推荐答案

示例表

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

获取用于动态数据透视的列，并将NULL替换为zero

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]'

您可以使用CUBE查找行和列的总数，并将NULL替换为Total，以表示CUBE生成的行.

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

结果

注意:如果要使用NULL代替zero作为值，请在动态数据透视代码中使用@cols代替@NulltoZeroCols

NOTE : If you want NULL instead of zero as values, use @cols instead of @NulltoZeroCols in dynamic pivot code

1.仅显示总行数

• 请勿使用代码SELECT @cols += ',[Total]'和SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'.
• 使用ROLLUP代替CUBE.
• Do not use the code SELECT @cols += ',[Total]' and SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'.
• Use ROLLUP instead of CUBE.

2.仅显示列总计

• 使用代码SELECT @cols += ',[Total]'和SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'.
• 使用ROLLUP代替CUBE.
• 将GROUP BY Location,PartCode更改为GROUP BY PartCode,Location.
• 使用WHERE PartCode<>''TOTAL'' ORDER BY PartCode代替ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode.
• Use the code SELECT @cols += ',[Total]' and SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'.
• Use ROLLUP instead of CUBE.
• Change GROUP BY Location,PartCode to GROUP BY PartCode,Location.
• Instead of ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode, use WHERE PartCode<>''TOTAL'' ORDER BY PartCode.

更新:将PartName用于OP

UPDATE : To bring PartName for OP

我正在更新以下查询，以将PartName添加到结果中.由于PartName将为CUBE添加更多结果，并避免在AND或OR条件下造成混淆，因此最好将透视结果与源表中的DISTINCT值结合在一起.

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屋！