如何实现带Group By的动态Pivot及动态列名与列列表

核心思路 #

要实现目标结构，需将Type1与Type2拼接为复合列名（如Short-A），基于这些复合列做动态透视。同时要确保所有可能的Type1+Type2组合都被包含（无对应数据时显示NULL），需先生成所有组合的笛卡尔积，再与原数据关联。

完整实现代码 #

-- 创建测试表并插入数据
create table Test
(
  name varchar(20) not null
, Type1 varchar(20) not null
, Type2 varchar(20) not null
, Value varchar(50) null
)

insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Short', 'A', 'loreipsum1')
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Short', 'B', 'loreipsum2')
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Long', 'A', 'loreipsum3')
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Long', 'B', 'loreipsum4')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Short', 'A', 'loreipsum5')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Short', 'B', 'loreipsum6')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Long', 'A', 'loreipsum7')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Long', 'B', 'loreipsum8')

select * into #Data from dbo.Test;

-- 生成所有Type1的固定取值
select x.* into #Typ1 from (select 'Short' Typ union select 'Long' Typ) x;
-- 生成动态的Type2取值（此处模拟A-C，实际可从数据源或参数获取）
select x.* into #Typ2 from (select 'A' Typ union select 'B' Typ union select 'C' Typ) x;

declare @PivotColumns nvarchar(max), @PivotQuery nvarchar(max);

-- 生成所有Type1+Type2的复合列名，格式为[Short-A], [Short-B]...
select @PivotColumns = STRING_AGG(QUOTENAME(CONCAT(t1.Typ, '-', t2.Typ)), ', ')
from #Typ1 t1
cross join #Typ2 t2;

-- 构建动态透视SQL：先生成所有Name+Type1+Type2的组合，左连接原数据确保无数据的列显示NULL
set @PivotQuery = N'
with AllCombinations as (
    -- 生成所有可能的Name、Type1、Type2组合
    select distinct d.name, t1.Typ as Type1, t2.Typ as Type2
    from #Data d
    cross join #Typ1 t1
    cross join #Typ2 t2
),
JoinedData as (
    -- 关联原数据，获取对应Value，无数据则为NULL
    select ac.name, CONCAT(ac.Type1, ''-'', ac.Type2) as PivotColumn, d.Value
    from AllCombinations ac
    left join #Data d on ac.name = d.name and ac.Type1 = d.Type1 and ac.Type2 = d.Type2
)
-- 动态透视复合列
select name, ' + @PivotColumns + N'
from JoinedData
pivot (
    max(Value)
    for PivotColumn in (' + @PivotColumns + N')
) as PVT;
';

-- 执行动态SQL
EXECUTE sp_executesql @PivotQuery;

-- 清理临时表
drop table #Data;
drop table #Typ1;
drop table #Typ2;
drop table Test;

关键步骤说明 #

• 生成复合列名：通过CROSS JOIN获取Type1和Type2的所有组合，用CONCAT拼接成Short-A格式，再用STRING_AGG生成透视所需的列列表。
• 确保无数据列显示NULL：通过AllCombinationsCTE生成所有可能的Name+Type1+Type2组合，再与原数据左连接，保证无对应数据的组合也能保留行并填充NULL。
• 动态透视：基于拼接后的PivotColumn做透视，用MAX聚合Value（因每个Name+Type1+Type2组合仅对应一条数据，聚合函数不影响结果）。

适配动态Type2的扩展 #

如果Type2的取值从数据源动态获取，只需修改#Typ2的生成逻辑，例如：

-- 从原表获取所有不重复的Type2值
select distinct Type2 as Typ into #Typ2 from #Data;
-- 若需包含原表没有的额外值（如C），可追加union：
-- select distinct Type2 as Typ from #Data union select 'C' as Typ;

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