SQL Server中无需聚合按值透视两列的实现问题

当前原始数据示例：

你之前用PIVOT+MAX没得到预期的按YEAR聚合的单行结果，通常是因为参与透视的数据集包含了除公共维度（A、B、C、D、YEAR）、透视列（Col）、值列（Value）之外的多余字段，PIVOT会自动按所有未在聚合、透视规则中声明的字段分组，清理多余字段后即可得到预期结果，以下是两种可行实现方案： #

方案1：静态PIVOT（已知Col字段所有取值） #

如果Col列的所有可能值可以提前明确，直接写固定查询语句即可：

SELECT 
    A, B, C, D, YEAR,
    E, F, HiBioInsec, HiChemInsec -- 此处替换为你所有Col字段的实际取值
FROM 你的原表名
PIVOT (
    MAX(Value) -- 若同分组下同个Col存在多个值，可按需替换为SUM/AVG等聚合函数
    FOR Col IN (E, F, HiBioInsec, HiChemInsec) -- 和上面SELECT的新列对应，列全所有Col取值
) AS PivotResult
ORDER BY YEAR

方案2：动态PIVOT（Col字段取值不固定） #

如果Col的取值会动态变化、或者取值太多不想手动罗列，可以拼接动态SQL实现：

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

-- 拼接所有要转成列的Col取值
SELECT @cols = STRING_AGG(QUOTENAME(Col), ',')
FROM (SELECT DISTINCT Col FROM 你的原表名) AS DistinctCols

-- 拼接完整PIVOT查询语句
SET @query = N'
SELECT A, B, C, D, YEAR, ' + @cols + N'
FROM 你的原表名
PIVOT (
    MAX(Value)
    FOR Col IN (' + @cols + N')
) AS PivotResult
ORDER BY YEAR
'

-- 执行动态SQL
EXEC sp_executesql @query

注意：如果相同A、B、C、D、YEAR分组下，同一个Col对应多个Value值，可根据业务需求把MAX替换为SUM/AVG/COUNT等合适的聚合函数即可。

内容的提问来源于stack exchange，提问作者Proffesore