怎样把做横列汇总报表？

[abc_sk]

declare @b table (a int,f varchar(3),r_q datetime ,b decimal(14,2),c decimal(14,2))


insert into @b select 1,'a','2005-01-01',100,200 union all
select 2,'B','2005-01-02',100,200

union select 3,'B','2005-04-01',100,200

select * from @B

1 a 2005-01-01 00:00:00.000 100.00 200.00
2 B 2005-01-02 00:00:00.000 100.00 200.00
3 B 2005-04-01 00:00:00.000 100.00 200.00



变成：


2005-01-01 2005-01-02 2005-04-01
a 100 0 0
b 0 100 0
b 0 0 100
c 200 200 200

[i9988]

这类问题太多了，不回答

[mugua604]

最后面的 c 是何物?

[splory]

--try
create table crt(a int,f varchar(3),r_q datetime ,b decimal(14,2),c decimal(14,2))

insert into crt select 1,'a','2005-01-01',100,200
union select 2,'B','2005-01-02',100,200

union select 3,'B','2005-04-01',100,200

select * from crt --drop table crt


declare @sql nvarchar(4000),@sql1 varchar(4000)
set @sql ='select f'
select @sql = @sql +',sum(case convert(varchar(10),r_q,120) when'''+convert(varchar(10),x.r_q,120)+''' then isnull(b,0) end) ['+convert(varchar(10),x.r_q,120)+']'
from (select distinct convert(varchar(10),r_q,120) r_q from crt) as x
set @sql = @sql +' from crt group by convert(varchar(10),r_q,120),f '
set @sql1 =' union select ''c'''
select @sql1 = @sql1 +',sum(case convert(varchar(10),r_q,120) when'''+convert(varchar(10),x.r_q,120)+''' then isnull(c,0) end)'
from (select distinct convert(varchar(10),r_q,120) r_q from crt) as x
set @sql1 = @sql1 +' from crt '
exec(@sql+@sql1)

[xyxfly]

create table B(a int,f varchar(3),r_q datetime ,b decimal(14,2),c decimal(14,2))


insert into B select 1,'a','2005-01-01',100,200 union all
select 2,'B','2005-01-02',100,200

union select 3,'B','2005-04-01',100,200

select * from B

Declare @S Varchar(8000),@s1 varchar(8000)
Select @S=''Select @S=@S+',SUM(Case r_q When '''+convert(varchar(10),r_q,120)+''' Then b Else 0 End) As '+'['+convert(varchar(10),r_q,120)+']'
From B
select @s1=''select @s1=@s1+',SUM(Case a When '''+rtrim(a)+''' Then c Else 0 End) As '+'['+rtrim(a)+']'
From B
Select @S1='Select [f]=''c'''+@S1+' From B group by c'
Select @S='Select B.f'+@S+' From B group by B.f,B.a union all '+@s1

EXEC(@S)