select code,名称, sum(case when ... then ... end ) as..., ... group by code,名称
不清楚N个join 具体是多少个
declare @sql varchar(8000) set @sql = 'select Code , 名称 ' select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then 状态 end) [状态' + cast(px as varchar) + ']' + ' , max(case px when ''' + cast(px as varchar) + ''' then 合计数1 end) [合计1_' + cast(px as varchar) + ']' + ' , max(case px when ''' + cast(px as varchar) + ''' then 合计数2 end) [合计2_' + cast(px as varchar) + ']' + ' , max(case px when ''' + cast(px as varchar) + ''' then 合计数3 end) [合计3_' + cast(px as varchar) + ']' from (select distinct px from (select t.* , px = (select count(1) from tb where Code = t.Code and 状态 < t.状态) + 1 from tb t) m) as a set @sql = @sql + ' from (select t.* , px = (select count(1) from tb where Code = t.Code and 状态 < t.状态) + 1 from tb t) m group by Code , 名称 ' exec(@sql)
select code,名称,
sum(case when ... then ... end ) as...,
...
group by code,名称
set @sql = 'select Code , 名称 '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then 状态 end) [状态' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then 合计数1 end) [合计1_' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then 合计数2 end) [合计2_' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then 合计数3 end) [合计3_' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select count(1) from tb where Code = t.Code and 状态 < t.状态) + 1 from tb t) m) as a
set @sql = @sql + ' from (select t.* , px = (select count(1) from tb where Code = t.Code and 状态 < t.状态) + 1 from tb t) m group by Code , 名称 '
exec(@sql)