sql

declare @nstardate varchar(10),@nenddate varchar(10),@s varchar(8000)
set @nstardate='2005-09-01'
set @nenddate='2005-11-01'
set @s=''
create table  #temp (zzwt numeric(9,3),zzcs char(10),zzdept nchar(20))
insert #temp
 select t_pjghc.zzwt,t_pjghc.zzcs,('過'+t_pjgh.zzdept) as zzdept  from t_pjgh,t_pjghc where t_pjghc.zzno=t_pjgh.zzno and t_pjgh.zzdate>=@nstardate and  t_pjgh.zzdate<=@nenddate
union all
select t_pjscc.zzwt,t_pjscc.zzcs,('收'+t_pjsc.zzdept) as zzdept  from t_pjsc,t_pjscc where t_pjscc.zzno=t_pjsc.zzno and t_pjsc.zzdate>=@nstardate and  t_pjsc.zzdate<=@nenddate
select * from #temp
select @s=@s+','+rtrim(ltrim(zzdept))+'=sum(case zzdept when '''+rtrim(ltrim(zzdept))+''' then zzwt else 0 end )' from #temp group by zzdept order by zzdept
print @s
set @s = 'select 成色=case when zzcs is null then ''合計'' else zzcs end'
         +@s
         +',合計=sum(zzwt) from #temp  group by zzcs order by zzcs '
print @s
exec(@s)
drop table #temp

create table #KhZy(ID varchar(10),KhMc  varchar(10),KhZtID varchar(10),YwR varchar(10),Sj datetime)
insert into #KhZy select '0001','客戶1','02','李三','2005-01-11'
insert into #KhZy select '0002','客戶2','03','王五','2005-01-11'
insert into #KhZy select '0003','客戶3','01','李三','2005-03-22'
insert into #KhZy select '0004','客戶4','02','王五','2005-01-11'
insert into #KhZy select '0005','客戶5','03','王五','2005-01-22'
insert into #KhZy select '0006','客戶6','01','李三','2005-02-11'
create table #KhZt(ID  varchar(10),KhZt varchar(10))
insert into #KhZt select '01','狀態1'
insert into #KhZt select '02','狀態2'
insert into #KhZt select '03','狀態3'

--?行??交叉表查?
declare @s varchar(8000),@date varchar(10)

set @s = ''
set @date = '2005-01-01'

select @s = @s + ','+KhZt+'=sum(case KhZtID when '''+ID+''' then 1 end)'
from #KhZt order by ID
print @s
set @s = 'select 業務員=case when YwR is null then ''合計'' else YwR end'
         +@s
         +',合計=count(*) into ##t from #KhZy where datediff(mm,sj,'''+@date+''')=0 group by YwR with rollup'
exec(@s)

select * from ##t

exec(@s)

select * from ##t

drop table #KhZt,#KhZy