sql-server-2008 – 动态数据透视表中的行和列总数

在SQL Server 2008中,我有一个包含3列的表(tblStock)：

• 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

如何创建程序以获得如下结果？

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,9,'WHs-C'
UNION ALL
SELECT 'D',2,'WHs-A'
UNION ALL
SELECT 'F',54,'WHs-E'
)TAB

获取用于动态旋转的列并将零替换为NULL

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('')),8000) 

SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'

您可以使用CUBE查找行和列总计,并将CUBE生成的行替换为NULL.

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查看结果

结果

注意：如果您想要NULL而不是零作为值,请在动态数据透视代码中使用@cols而不是@NulltoZeroCols

编辑：

1.仅显示行总计

>不要使用代码SELECT @cols =’,[Total]’和SELECT @NullToZeroCols =’,0)AS [Total]’.
>使用ROLLUP而不是CUBE.

2.仅显示列总计

>使用代码SELECT @cols =’,0)AS [Total]’.
>使用ROLLUP而不是CUBE.
>将GROUP BY位置,PartCode更改为GROUP BY PartCode,Location.
>而不是ORDER BY CASE WHEN(PartCode =”Total”)THEN 1 ELSE 0 END,PartCode,使用WHERE PartCode<>”TOTAL”按PartCode排序.

更新：为OP带来PartName

我正在更新以下查询以添加带有结果的PartName.由于PartName将使用CUBE添加额外的结果并避免在AND或OR条件中出现混淆,因此最好将轮转结果与源表中的DISTINCT值相结合.

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT P.PartCode,T.PartName,' + @NulltoZeroCols + ' FROM 
             (                
                 SELECT 
                 ISNULL(CAST(PartCode AS VARCHAR(30)),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查看结果

（编辑：上饶站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!