如何在SQL PIVOT的IN子句中动态生成基于MIN/MAX的字段列表

静态SQL无法直接实现动态生成PIVOT的IN子句，必须借助动态SQL完成，以下是适配SQL Server环境的具体实现步骤：

步骤1：生成符合格式的Code字段列表 #

先获取code的起止范围，再生成PIVOT需要的带方括号的字段格式：

DECLARE @MinCode CHAR(1), @MaxCode CHAR(1)
DECLARE @PivotColumns NVARCHAR(MAX) = ''

-- 获取起止Code值
SELECT @MinCode = MIN(code), @MaxCode = MAX(code) FROM [table]

-- 生成连续Code的方括号格式列表（适用于字母/数字连续的场景）
;WITH CodeRange AS (
    SELECT ASCII(@MinCode) AS CodeASCII
    UNION ALL
    SELECT CodeASCII + 1 FROM CodeRange
    WHERE CodeASCII < ASCII(@MaxCode)
)
SELECT @PivotColumns += QUOTENAME(CHAR(CodeASCII)) + ','
FROM CodeRange

-- 移除最后多余的逗号
SET @PivotColumns = LEFT(@PivotColumns, LEN(@PivotColumns) - 1)

如果code是离散非连续值，替换上述CTE部分为去重查询：

SELECT @PivotColumns += QUOTENAME(code) + ','
FROM (SELECT DISTINCT code FROM [table] WHERE code BETWEEN @MinCode AND @MaxCode) t

步骤2：拼接并执行完整PIVOT查询 #

将生成的字段列表拼接到原SQL中，通过系统存储过程执行动态语句：

DECLARE @DynamicSQL NVARCHAR(MAX) = N'
SELECT * FROM
(SELECT 
    cmdoc.documentno ''DRG. NO.'',
    cmdoc.subject ''TITLE'',
    CAST(documentno AS VARCHAR(255)) AS docno, 
    CAST(cmrev.code AS VARCHAR(255)) AS code
FROM cmdocument cmdoc
INNER JOIN cmdocumentrevision cmrev
    ON cmdoc.cmdocument = cmrev.cmdocument
INNER JOIN cmdocumentdefn
    ON cmdoc.cmdocumentdefn = cmdocumentdefn.cmdocumentdefn
WHERE 
cmdocumentdefn.isrevisable = 1 
) x
PIVOT(COUNT(docno) FOR code IN(' + @PivotColumns + ')) pvt'

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL

注意事项 #

• 替换代码中的[table]为你实际存储code值的表名
• 如果code是数字类型，调整变量类型和ASCII转换逻辑即可

内容的提问来源于stack exchange，提问作者Leonardo Vicente Rapirap