pivot不支持动态列名，必须用动态sql拼接；其in子句仅接受字面量标识符，不能为子查询、变量或表达式，需先查出列值、quotename处理、拼接完整语句再执行。
PIVOT 本身不支持动态列名，所谓“动态行转列”必须用动态 SQL 拼接，不能靠 PIVOT 语法本身解决。
PIVOT 为什么不能直接动态列
PIVOT 的 IN 子句要求硬编码列值，例如 ([Math], [English], [Science])。
SQL Server 在编译阶段就要确定结果集结构，而列名属于元数据，无法在运行时由查询自动推导。
常见错误现象：
- 直接写
SELECT ... IN (SELECT DISTINCT subject FROM scores)→ 报错：ERROR: Incorrect syntax near 'SELECT' - 用变量代替
IN列表 → 报错：Must declare the scalar variable "@cols"
关键限制：
-
IN里不能是子查询、变量、表达式，只能是字面量标识符 - 即使你用
STRING_AGG(QUOTENAME(...), ', ')构造出字符串，也必须拼进完整 SQL 字符串再执行 -
PIVOT不会自动补全缺失组合，某user_id没有'Chemistry'成绩，对应列就是NULL，不会跳过或报错
如何安全拼接动态 PIVOT SQL
核心思路：先查出所有目标列值 → 构造带方括号的列名列表 → 拼成完整 PIVOT 查询 → 执行。
示例场景：按 product 行转列，聚合 sum(qty)，分组键为 salesdate, region
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
-- 1. 获取去重 product 值，并 QUOTENAME 防注入/空格/特殊字符
SELECT @cols = STRING_AGG(QUOTENAME(product), ', ')
FROM (SELECT DISTINCT product FROM sales) AS p;
-- 2. 拼接完整语句（注意：不能漏掉源子查询别名、PIVOT 别名、ORDER BY）
SET @query = N'
SELECT salesdate, region, ' + @cols + '
FROM (
SELECT salesdate, region, product, qty FROM sales
) AS src
PIVOT (
SUM(qty) FOR product IN (' + @cols + ')
) AS pvt
ORDER BY salesdate, region;';
-- 3. 打印验证（上线前必做）
PRINT @query;
-- 4. 执行
EXEC sp_executesql @query;
注意事项：
- 必须用
N'前缀声明 Unicode 字符串，否则中文列名可能乱码 -
QUOTENAME不仅防 SQL 注入，还能处理含空格、短横线、数字开头的列名（如[2025-Q1]） - 如果
@cols为空（比如sales表无数据），@query会拼出语法错误，建议加IF @cols IS NULL RETURN守卫 -
STRING_AGG要求 SQL Server 2017+；旧版本需用FOR XML PATH替代
动态列下如何处理 NULL 和默认值
PIVOT 输出列默认为 NULL，但报表常需 0 或空字符串。
错误做法：
- 在
PIVOT内部加ISNULL→ 语法不支持，报错
正确做法：
- 在最终
SELECT列表中包装每列，例如：SELECT salesdate, region, ISNULL([a], 0) AS a, ISNULL([b], 0) AS b, ISNULL([c], 0) AS c FROM (...) AS pvt;
- 但动态场景下，你无法预知列名，所以得在拼
@query时一并生成这些ISNULL包装：SELECT @cols = STRING_AGG('ISNULL(' + QUOTENAME(product) + ', 0) AS ' + QUOTENAME(product), ', ') FROM (SELECT DISTINCT product FROM sales) AS p;
真正容易被忽略的一点
动态 SQL 不会自动继承外层事务、SET 选项（如 ANSI_NULLS）、或上下文权限。
如果你在存储过程中调用它，且该过程以 EXECUTE AS 运行，动态部分仍按调用者权限执行 —— 权限不足时静默失败或报错 The SELECT permission was denied。
务必在 sp_executesql 前显式设置必要选项，或改用 EXECUTE AS CALLER 显式控制。
另外，每次执行都触发一次查询计划编译，高频调用时可能成为性能瓶颈；若列集合变化不频繁，可考虑缓存拼好的 SQL 字符串或用临时表预聚合。
