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,200select * 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
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,200select * 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
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,200select * 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)
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,200select * from BDeclare @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 '+@s1EXEC(@S)