SQL动态列Pivot查询详解

关于如何实现动态列的Pivot查询，一个非常优秀的资源来自Itzik Ben-Gan的示例。他不仅提供了详细的代码示例，还在Stack Overflow上分享了一个相关的问题，其中详细解释了实现动态Pivot所需的步骤。通常，这涉及到使用动态SQL来构建查询语句。

强烈推荐Itzik Ben-Gan的《Inside Microsoft SQL Server 2008: T-SQL Programming》一书，书中深入讲解了如何构建动态Pivot查询，并提供了丰富的实例和最佳实践。

下面是一个简单的例子，演示如何创建和填充订单表，并执行静态和动态Pivot查询：

USE tempdb;
GO
IF OBJECT_ID('dbo.Orders') IS NOT NULL
DROP TABLE dbo.Orders;
GO
CREATE TABLE dbo.Orders
(
orderid int NOT NULL PRIMARY KEY NONCLUSTERED,
orderdate datetime NOT NULL,
empid int NOT NULL,
custid varchar(5) NOT NULL,
qty int NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid ON dbo.Orders(orderdate, orderid);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty) VALUES
(30001, '20020802', 3, 'A', 10),
(10001, '20021224', 1, 'A', 12),
(10005, '20021224', 1, 'B', 20),
(40001, '20030109', 4, 'A', 40),
(10006, '20030118', 1, 'C', 14),
(20001, '20030212', 2, 'B', 12),
(40005, '20040212', 4, 'A', 10),
(20002, '20040216', 2, 'C', 20),
(30003, '20040418', 3, 'B', 15),
(30004, '20020418', 3, 'C', 22),
(30007, '20020907', 3, 'D', 30);
GO
-- 静态PIVOT查询
SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR orderyear IN([2002], [2003], [2004])) AS P;
GO
-- 动态PIVOT查询
DECLARE @cols AS NVARCHAR(MAX),
@sql AS NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(YEAR(orderdate)), ', ')
FROM (SELECT DISTINCT YEAR(orderdate) AS orderdate FROM dbo.Orders) AS D;
SET @sql = N'SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P;';
EXEC sp_executesql @sql;
GO