这个动态交叉表如何弄

这个动态交叉表怎么弄

资产编号 年 金额
001 2001 50
001 2002 30
002 2001 20
002 2002 30
002 2003 40



我想实现
编号 2001 2002 2003
001 50 30 0
001 20 30 40
合计 70 60 40

[解决办法]

SQL code

--> 测试数据：#if object_id('tempdb.dbo.#') is not null drop table #create table #(id varchar(8), year int, money int)insert into #select '001', 2001, 50 union allselect '001', 2002, 30 union allselect '002', 2001, 20 union allselect '002', 2002, 30 union allselect '002', 2003, 40declare @cols varchar(8000)select @cols=isnull(@cols+',','')+'['+ltrim(year)+']=sum(case year when '+ltrim(year)+' then money else 0 end)' from # group by yearexec ('select id, '+@cols+' from (select * from # union all select ''sum'', year, sum(money) from # group by year) t group by id')/*id       2001        2002        2003-------- ----------- ----------- -----------001      50          30          0002      20          30          40sum      70          60          40*/
[解决办法]
SQL code
--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([资产编号] varchar(3),[年] int,[金额] int)insert [tbl]select '001',2001,50 union allselect '001',2002,30 union allselect '002',2001,20 union allselect '002',2002,30 union allselect '002',2003,40declare @str varchar(1000)set @str=''select @str=@str+','+'['+ltrim([年])+'年'+']'+'=sum(case when [年]='+QUOTENAME([年],'''')+' then [金额] else 0 end)'from tbl group by [年]print @strexec('select [资产编号] as 编号'+@str+' from tbl group by 资产编号')/*编号    2001年    2002年    2003年001    50    30    0002    20    30    40*/
[解决办法]
SQL code
--如果年份固定为2001,2002,2003，则使用如下的静态SQL。select isnull(资产编号,'合计') 编号 ,        max(case 年 when '2001' then 金额 else 0 end) [2001],       max(case 年 when '2002' then 金额 else 0 end) [2002],       max(case 年 when '2003' then 金额 else 0 end) [2003]from tbgroup by 资产编号 with rollup--如果年份不固定，则使用如下的动态SQL。declare @sql varchar(8000)set @sql = 'select isnull(资产编号,''合计'') 编号 'select @sql = @sql + ' , max(case 年 when ''' + 年 + ''' then 金额 else 0 end) [' + 年 + ']'from (select distinct 年 from tb) as aset @sql = @sql + ' from tb group by 资产编号 with rollup'exec(@sql)
[解决办法]
SQL code
create table tb([资产编号] varchar(10),[年] varchar(10),[金额] int)insert tb select '001',2001,50 union allselect '001',2002,30 union allselect '002',2001,20 union allselect '002',2002,30 union allselect '002',2003,40--如果年份固定为2001,2002,2003，则使用如下的静态SQL。select isnull(资产编号,'合计') 编号 ,        sum(case 年 when '2001' then 金额 else 0 end) [2001],       sum(case 年 when '2002' then 金额 else 0 end) [2002],       sum(case 年 when '2003' then 金额 else 0 end) [2003]from tbgroup by 资产编号 with rollup/*编号         2001        2002        2003        ---------- ----------- ----------- ----------- 001        50          30          0002        20          30          40合计         70          60          40（所影响的行数为 3 行）*/--如果年份不固定，则使用如下的动态SQL。declare @sql varchar(8000)set @sql = 'select isnull(资产编号,''合计'') 编号 'select @sql = @sql + ' , sum(case 年 when ''' + 年 + ''' then 金额 else 0 end) [' + 年 + ']'from (select distinct 年 from tb) as aset @sql = @sql + ' from tb group by 资产编号 with rollup'exec(@sql) /*编号         2001        2002        2003        ---------- ----------- ----------- ----------- 001        50          30          0002        20          30          40合计         70          60          40*/drop table tb