SQL Server 动态 PIVOT 查询？

Question

我的任务是想出一种翻译以下数据的方法：

 date        category        amount
1/1/2012    ABC             1000.00
2/1/2012    DEF             500.00
2/1/2012    GHI             800.00
2/10/2012   DEF             700.00
3/1/2012    ABC             1100.00

进入以下：

 date        ABC             DEF             GHI
1/1/2012    1000.00
2/1/2012                    500.00
2/1/2012                                    800.00
2/10/2012                   700.00
3/1/2012    1100.00

空白点可以是 NULL 或空白，两者都可以，并且类别需要是动态的。另一个可能的警告是，我们将以有限的容量运行查询，这意味着临时表已用完。我已经尝试研究并已登陆 PIVOT 但因为我之前从未使用过它，我真的不明白它，尽管我尽了最大努力去弄清楚它。谁能指出我正确的方向？

原文由 Sean Cunningham 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

动态 SQL PIVOT：

 create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category)
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '

execute(@query)

drop table temp

结果：

 Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL

原文由 Taryn 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

具有性能改进的 Taryn 答案的 一个版本：

数据

CREATE TABLE dbo.Temp
(
    [date] datetime NOT NULL,
    category nchar(3) NOT NULL,
    amount money NOT NULL,

    INDEX [CX dbo.Temp date] CLUSTERED ([date]),
    INDEX [IX dbo.Temp category] NONCLUSTERED (category)
);

INSERT dbo.Temp
    ([date], category, amount)
VALUES
    ({D '2012-01-01'}, N'ABC', $1000.00),
    ({D '2012-01-02'}, N'DEF', $500.00),
    ({D '2012-01-02'}, N'GHI', $800.00),
    ({D '2012-02-10'}, N'DEF', $700.00),
    ({D '2012-03-01'}, N'ABC', $1100.00);

动态枢轴

DECLARE
    @Delimiter nvarchar(4000) = N',',
    @DelimiterLength bigint,
    @Columns nvarchar(max),
    @Query nvarchar(max);

SET @DelimiterLength = LEN(REPLACE(@Delimiter, SPACE(1), N'#'));

-- Before SQL Server 2017
SET @Columns =
    STUFF
    (
        (
            SELECT
                [text()] = @Delimiter,
                [text()] = QUOTENAME(T.category)
            FROM dbo.Temp AS T
            WHERE T.category IS NOT NULL
            GROUP BY T.category
            ORDER BY T.category
            FOR XML PATH (''), TYPE
        )
        .value(N'text()[1]', N'nvarchar(max)'),
        1, @DelimiterLength, SPACE(0)
    );

-- Alternative for SQL Server 2017+ and database compatibility level 110+
SELECT @Columns =
    STRING_AGG(CONVERT(nvarchar(max), QUOTENAME(T.category)), N',')
        WITHIN GROUP (ORDER BY T.category)
FROM
(
    SELECT T2.category
    FROM dbo.Temp AS T2
    WHERE T2.category IS NOT NULL
    GROUP BY T2.category
) AS T;

IF @Columns IS NOT NULL
BEGIN
    SET @Query =
        N'SELECT [date], ' +
        @Columns +
        N'
        FROM
        (
            SELECT [date], amount, category
            FROM dbo.Temp
        ) AS S
        PIVOT
        (
            MAX(amount)
            FOR category IN (' +
            @Columns +
            N')
        ) AS P;';

    EXECUTE sys.sp_executesql @Query;
END;

执行计划

结果

日期美国广播公司国防军全球健康指数2012-01-01 00:00:00.000 1000.00无效的无效的2012-01-02 00:00:00.000无效的500.00 800.00 2012-02-10 00:00:00.000无效的700.00无效的2012-03-01 00:00:00.000 1100.00无效的无效的

原文由 Paul White 发布，翻译遵循 CC BY-SA 4.0 许可协议