请教个复杂的SQL语句
表XS
dq     je       sz
a       100     2007.1.1
a       10       2007.1.1
b       100     2007.1.1
a       50       2007.1.2
a       10       2007.1.31
b       60       2007.2.1

怎么查出这样的形式
dq     je总和       1     2     3     4   一直到31(每天)


------解决方案--------------------
create table tb(dq varchar(10),je int,sz datetime)
insert into tb values( 'a ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 10 , '2007.1.1 ')
insert into tb values( 'b ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 50 , '2007.1.2 ')
insert into tb values( 'a ', 10 , '2007.1.31 ')
insert into tb values( 'b ', 60 , '2007.2.1 ')
go

declare @sql varchar(8000)
set @sql = 'select dq '
select @sql = @sql + ' , sum(case datepart(day,sz) when ' ' ' + cast(rq as varchar) + ' ' ' then je else 0 end) [ ' + cast(rq as varchar) + '] '
from (select distinct datepart(day,sz) rq from tb) as a
set @sql = @sql + ' from tb group by dq '
exec(@sql)

drop table tb

/*
dq 1 2 31
---------- ----------- ----------- -----------
a 110 50 10
b 160 0 0
*/
------解决方案--------------------
--不知道加个月份是不是要好点?

create table tb(dq varchar(10),je int,sz datetime)
insert into tb values( 'a ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 10 , '2007.1.1 ')
insert into tb values( 'b ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 50 , '2007.1.2 ')
insert into tb values( 'a ', 10 , '2007.1.31 ')
insert into tb values( 'b ', 60 , '2007.2.1 ')
go

declare @sql varchar(8000)
set @sql = 'select dq,convert(varchar(7),sz,120) 月份 '
select @sql = @sql + ' , sum(case datepart(day,sz) when ' ' ' + cast(rq as varchar) + ' ' ' then je else 0 end) [ ' + cast(rq as varchar) + '] '
from (select distinct datepart(day,sz) rq from tb) as a
set @sql = @sql + ' from tb group by dq,convert(varchar(7),sz,120) '
exec(@sql)

drop table tb

/*
dq 月份 1 2 31
---------- ------- ----------- ----------- -----------
a 2007-01 110 50 10
b 2007-01 100 0 0
b 2007-02 60 0 0
*/
------解决方案--------------------
create table tb(dq varchar(10),je int,sz datetime)
insert into tb values( 'a ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 10 , '2007.1.1 ')
insert into tb values( 'b ', 100 , '2007.1.1 ')
insert into tb values( 'a ', 50 , '2007.1.2 ')
insert into tb values( 'a ', 10 , '2007.1.31 ')
insert into tb values( 'b ', 60 , '2007.2.1 ')
go

declare @sql varchar(8000)
set @sql = 'select dq,sum(je)je总和 '
select @sql = @sql + '