T-SQL Pivot - 总行和动态列

让我们直接进入正题。这是代码

SELECT [prov], [201304], [201305], [201306], [201307]
FROM (
SELECT [prov], [arrival], [Amount]
FROM [tblSource]) up
PIVOT (SUM([Amount]) FOR [arrival] IN ([201304], [201305], [201306], [201307])) AS pvt
GO

它让我想起了一张如此可爱的桌子。我想知道如何获得每个“日期”列的总计以显示在附加的最后一行中？

此外，基础表将添加更多数据，特别是更多日期。这意味着201308接下来将添加，然后201309 etc

这意味着目前我必须每月修改上面的代码以反映添加的内容。有没有办法解决？

您可以使用动态 SQL 动态创建列，但是，我强烈建议在为其设计的层（例如 SSRS 或 excel）中处理动态数据透视表。

DECLARE @SQL NVARCHAR(MAX) = '',
        @SQL2 NVARCHAR(MAX) = '',
        @SQL3 NVARCHAR(MAX) = '';

-- COMPILE THE UNIQUE VALUES FOR ARRIVAL THAT NEED TO BE PIVOTED
SELECT  @SQL = @SQL + ',' + QUOTENAME(Arrival),
        @SQL2 = @SQL2 + '+ISNULL(' + QUOTENAME(Arrival) + ', 0)',
        @SQL3 = @SQL3 + ',' + QUOTENAME(Arrival) + ' = ISNULL(' + QUOTENAME(Arrival) + ', 0)'
FROM    (SELECT DISTINCT Arrival FROM tblSource) s;

-- COMBINE THEM INTO A SINGLE QUERY
SET @SQL = 'SELECT [Prov]' + @SQL3 + ', [Total] = ' + STUFF(@SQL2, 1, 1, '') + '
            FROM    (   SELECT  Arrival, Prov, Amount
                        FROM    [tblSource]
                        UNION ALL
                        SELECT  Arrival, ''Total'', SUM(Amount)
                        FROM    [tblSource]
                        GROUP BY Arrival
                    ) up
                    PIVOT
                    (   SUM(Amount)
                        FOR Arrival IN (' + STUFF(@SQL, 1, 1, '') + ')
                    ) pvt;';

-- EXECUTE THE QUERY
EXECUTE SP_EXECUTESQL @SQL;

这将创建并执行以下 SQL：

SELECT  [Prov],
        [2013-01-01] = ISNULL([2013-01-01], 0),
        [2013-02-01] = ISNULL([2013-02-01], 0), 
        [Total] = ISNULL([2013-01-01], 0) + ISNULL([2013-02-01], 0)
FROM    (   SELECT  Arrival, Prov, Amount
            FROM    [tblSource]
            UNION ALL
            SELECT  Arrival, 'Total', SUM(Amount)
            FROM    [tblSource]
            GROUP BY Arrival
        ) up
        PIVOT
        (   SUM(Amount)
            FOR Arrival IN ([2013-01-01],[2013-02-01])
        ) pvt;

就是子查询中union下面的查询up它将总计行添加到底部，而行总计只是通过添加该行中的所有列来创建。

SQL Fiddle 示例

但我要再次强调，我真的建议在 SQL 之外处理这样的数据操作。

EDIT

使用 UNION 获取总行的另一种方法是使用GROUPING SETS如下：

DECLARE @SQL NVARCHAR(MAX) = '',
        @SQL2 NVARCHAR(MAX) = '',
        @SQL3 NVARCHAR(MAX) = '';

-- COMPILE THE UNIQUE VALUES FOR ARRIVAL THAT NEED TO BE PIVOTED
SELECT  @SQL = @SQL + ',' + QUOTENAME(Arrival),
        @SQL2 = @SQL2 + '+ISNULL(' + QUOTENAME(Arrival) + ', 0)',
        @SQL3 = @SQL3 + ',' + QUOTENAME(Arrival) + ' = ISNULL(' + QUOTENAME(Arrival) + ', 0)'
FROM    (SELECT DISTINCT Arrival FROM tblSource) s;

-- COMBINE THEM INTO A SINGLE QUERY
SET @SQL = 'SELECT [Prov]' + @SQL3 + ', [Total] = ' + STUFF(@SQL2, 1, 1, '') + '
            FROM    (   SELECT  Arrival, Prov = ISNULL(Prov, 'Total'), Amount = SUM(Amount)
                        FROM    [tblSource]
                        GROUP BY GROUPING SETS((Prov, arrival), (arrival))
                    ) up
                    PIVOT
                    (   SUM(Amount)
                        FOR Arrival IN (' + STUFF(@SQL, 1, 1, '') + ')
                    ) pvt;';

-- EXECUTE THE QUERY
EXECUTE SP_EXECUTESQL @SQL;