if object_id('tempdb..#tmp') is not null drop table #tmp GO ----创建测试数据 create table #tmp(week int,数量 int,利率 decimal(10,2)) insert #tmp select 601, 100, 0.12 union all select 602, 210, 0.25 ----静态汇总 select [601] = cast(sum(case week when 601 then 数量 else 0 end) as varchar(10)), [602] = cast(sum(case week when 602 then 数量 else 0 end) as varchar(10)) from #tmp union all select [601] = cast(sum(case week when 601 then 利率 else 0 end) as varchar(10)), [602] = cast(sum(case week when 602 then 利率 else 0 end) as varchar(10)) from #tmp ----动态汇总 declare @str1 varchar(8000),@str2 varchar(8000) set @str1 = '' set @str2 = '' select @str1 = @str1 + ',[' + rtrim(week) + '] = cast(sum(case week when ' + rtrim(week) + ' then 数量 else 0 end) as varchar(10))', @str2 = @str2 + ',[' + rtrim(week) + '] = cast(sum(case week when ' + rtrim(week) + ' then 利率 else 0 end) as varchar(10))' from (select distinct week from #tmp) a select @str1 = stuff(@str1,1,1,''),@str2 = stuff(@str2,1,1,'')EXEC('select ' + @str1 + ' from #tmp union all select ' + @str2 + ' from #tmp ')----清除测试数据 drop table #tmp
drop table #tmp
GO
----创建测试数据
create table #tmp(week int,数量 int,利率 decimal(10,2))
insert #tmp
select 601, 100, 0.12 union all
select 602, 210, 0.25
----静态汇总
select
[601] = cast(sum(case week when 601 then 数量 else 0 end) as varchar(10)),
[602] = cast(sum(case week when 602 then 数量 else 0 end) as varchar(10))
from #tmp
union all
select
[601] = cast(sum(case week when 601 then 利率 else 0 end) as varchar(10)),
[602] = cast(sum(case week when 602 then 利率 else 0 end) as varchar(10))
from #tmp
----动态汇总
declare @str1 varchar(8000),@str2 varchar(8000)
set @str1 = ''
set @str2 = ''
select
@str1 = @str1 + ',[' + rtrim(week) + '] = cast(sum(case week when ' + rtrim(week) + ' then 数量 else 0 end) as varchar(10))',
@str2 = @str2 + ',[' + rtrim(week) + '] = cast(sum(case week when ' + rtrim(week) + ' then 利率 else 0 end) as varchar(10))'
from (select distinct week from #tmp) a
select @str1 = stuff(@str1,1,1,''),@str2 = stuff(@str2,1,1,'')EXEC('select ' + @str1 + ' from #tmp union all select ' + @str2 + ' from #tmp ')----清除测试数据
drop table #tmp