Taryn的答案的一个版本的性能改进:
Data
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;
执行计划
结果
| date |
ABC |
DEF |
GHI |
| 2012-01-01 00:00:00.000 |
1000.00 |
NULL |
NULL |
| 2012-01-02 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 |
