如何从数据透视表创建"Total"列和页脚？

Question

我有一个包含warehouse, product, and available列的库存表，看起来像：

warehouse    product    available
John2196     KITCOMP01    7
John2196     KITCOMP01    12
John2196     KITCOMP02    7
JohnS196     KITCOMP01    9
JohnS196     KITCOMP03    1

仓库列被旋转以创建这个：

product    John2196    JohnS196
KITCOMP01     19          9
KITCOMP02     7          NULL
KITCOMP03    NULL         1

使用此代码

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Warehouse) 
                from tlninventory
                group by Warehouse
                order by Warehouse
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') ,1,1,'')
set @query = 'SELECT product,' + @cols + ' from 
         (
            select product, warehouse, available
            from tlninventory
        ) x
        pivot 
        (
            sum(available)
            for warehouse in (' + @cols + ')
        ) p '
execute(@query)

现在，我需要获得一个Total Column和Total Footer Row，同时忽略NULL值，但由于标题是通过透视创建的，并且此查询将在具有不同仓库和产品的多个表上运行，这意味着标题永远不会相同，因此我无法对静态列求和，如本线程所建议的。

作为参考，我需要一个最后的表格，看起来像：

product    John2196    JohnS196    Total
KITCOMP01     19          9         28
KITCOMP02     7          NULL       7
KITCOMP03    NULL         1         1
Total         26          10        36

Answer 1

您需要在这里做一些事情。

首先创建一个新变量来存储"sum（warehouse）"列

DECLARE @cols AS NVARCHAR(MAX),
        @sumCols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

创建类似于常规数据透视列名的合计列

select @sumCols = STUFF((SELECT ',' + 'SUM(' + QUOTENAME(Warehouse) + ')' + QUOTENAME(Warehouse)
                from tlninventory
                group by Warehouse
                order by Warehouse
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

现在，您需要创建透视查询的cte，但添加一个带窗口的sum() over()来获取行总数。。

set @query = ' WITH cte AS  
        (
            SELECT product,' + @cols + ', Total from 
             (
                select product, warehouse, available, sum(available) over (partition by product) total
                from tlninventory
            ) x
            pivot 
            (
                sum(available)
                for warehouse in (' + @cols + ')
            ) p 
        ) 

现在，使用sum列在末尾并集一个total列。

set @query = ' WITH cte AS  
        (
            SELECT product,' + @cols + ', Total from 
             (
                select product, warehouse, available, sum(available) over (partition by product) total
                from tlninventory
            ) x
            pivot 
            (
                sum(available)
                for warehouse in (' + @cols + ')
            ) p 
        ) 
        SELECT product,' + @cols + ', Total FROM (
            SELECT *, ''a'' sortCol 
            FROM cte 
            UNION ALL 
            SELECT ''Total'', ' + @sumCols + ', sum(total), ''z''
            FROM cte 
        ) a
        ORDER BY sortCol, product'
execute(@query)

SQL Fiddle