SQL Server使用Pivot实现多聚合值行转列问题求解

解决方案 #

SQL Server原生PIVOT单次仅支持聚合一个字段，你可以通过**先逆透视(UNPIVOT)再转置(PIVOT)**的方式实现单查询同时转出score和finalscore两个指标的所有分类值，完整可运行代码如下：

-- 建表&测试数据和原有逻辑一致
create table temp
(
    PID int,
    category varchar(max),
    score int,
    finalscore int,
)

insert into temp values (1, 'A', 50,20)
insert into temp values (1, 'B', 30,10)
insert into temp values (1, 'C', 40,20)
insert into temp values (1, 'D', 60,70)
insert into temp values (1, 'E', 55,25)


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

-- 动态生成所有转置后的列：格式为 分类_指标，例如A_score、A_finalscore
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category + '_score') + ',' + QUOTENAME(c.category + '_finalscore')
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


SET @query='select PID,'+ @cols +' from
        (
            -- 先做逆透视：把score、finalscore两个列转为行记录，同时拼接生成转置用的列名
            select 
                PID,
                category + ''_'' + val_type as col_name,
                val
            from temp
            unpivot
            (
                val for val_type in (score, finalscore)
            ) unpvt
        )x
        pivot
        (
            max(val) for col_name in('+ @cols +')
        )p
'

exec (@query)

drop table temp

逻辑说明 #

• 先通过UNPIVOT将原本横向存储的score和finalscore两个指标转为纵向行数据，同时把分类值和指标名称拼接成唯一的列标识，例如A分类的普通分就拼接为A_score
• 再对拼接后的列标识做PIVOT聚合，一次就可以转出所有分类的两个分数字段，符合预期输出要求
  如果不想用UNPIVOT，也可以分别写两次PIVOT查询单独转出score和finalscore的结果，再通过PID字段关联两个结果集，写法的复杂度和执行效率略低于上述方案。

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