MS SQL多id列长表转宽表的PIVOT通用实现方案咨询

核心逻辑：PIVOT转换时的待旋转列值需要和动态生成的列名完全匹配，你只需要将多个_id字段拼接为同一个标识字段，再用该字段作为PIVOT的旋转维度即可，该方案可以直接扩展到N个_id字段的场景。

修正后的动态SQL代码 #

declare @cols nvarchar(max);
declare @sql  nvarchar(max);

-- 生成动态列名：多个_id字段按自定义规则拼接即可
select @cols = stuff((
    select distinct ', ' + 'VarA_'+convert(varchar(10),varA_id) + '_VarB_'+convert(varchar(10),varB_id)
    from t
    order by 1
    for xml path (''), type).value('.','nvarchar(max)')
,1,2,'')

set @sql = '
select Id, date, ' + @cols + '
from (
    select 
        Id, 
        date,
        -- 此处拼接规则必须和上方生成列名的规则完全一致
        pivot_col = ''VarA_''+convert(varchar(10),varA_id) + ''_VarB_''+convert(varchar(10),varB_id),
        value
    from t
) as source
pivot (
    sum([value]) 
    for pivot_col in (' + @cols + ') 
) as pivot_result'

exec(@sql);

多ID字段扩展方法 #

如果你的业务表有3个甚至更多_id字段（例如新增varC_id），仅需修改两处拼接规则即可适配：

• 动态列名生成部分的拼接逻辑，新增对应_id字段的拼接规则
• 子查询中pivot_col字段的拼接逻辑和列名生成规则保持完全一致

注意事项 #

如果业务表不需要保留id、date这类非聚合维度字段，直接在子查询和外层select语句中删除对应字段即可，PIVOT会自动按剩余的非旋转、非聚合字段分组。

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