Declare @sql varchar(8000)
select @sql='';
select @sql='select departmentid,departmentname,staffid,staffname,monthid'
select @sql= @sql + ' ,isnull( ( select salaryreportitemresult from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid and a.ReportItemName=''' + reportitemname + ''' ) ,0) as ' + reportitemname from CustomerSalaryReportItems
select @sql= @sql + ' , isnull( ( select sum(isnull(salaryreportitemresult,0)) from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid ),0) as 金额小计 '
select @sql=@sql+ ' from #TMPSRTR group by departmentid,departmentname,staffid,staffname,monthid '
exec(@SQL)
以上是一个动态的结果(相当于行转列吧)
下面是上面动态代码运行出来的一个结果,也就是动态代码中如果只有一列的情况
select departmentid,departmentname,staffid,staffname,monthid ,
isnull( ( select salaryreportitemresult from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid and a.ReportItemName='电费1' ),0) as 电费1from #TMPSRTR group by departmentid,departmentname,staffid,staffname,monthid
现在我想要在上面的结果之上再进行按 departmentname 计算出小计求合及所有数据的总计列!
请各位指点!
select @sql='';
select @sql='select departmentid,departmentname,staffid,staffname,monthid'
select @sql= @sql + ' ,isnull( ( select salaryreportitemresult from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid and a.ReportItemName=''' + reportitemname + ''' ) ,0) as ' + reportitemname from CustomerSalaryReportItems
select @sql= @sql + ' , isnull( ( select sum(isnull(salaryreportitemresult,0)) from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid ),0) as 金额小计 '
select @sql=@sql+ ' from #TMPSRTR group by departmentid,departmentname,staffid,staffname,monthid '
exec(@SQL)
以上是一个动态的结果(相当于行转列吧)
下面是上面动态代码运行出来的一个结果,也就是动态代码中如果只有一列的情况
select departmentid,departmentname,staffid,staffname,monthid ,
isnull( ( select salaryreportitemresult from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid and a.ReportItemName='电费1' ),0) as 电费1from #TMPSRTR group by departmentid,departmentname,staffid,staffname,monthid
现在我想要在上面的结果之上再进行按 departmentname 计算出小计求合及所有数据的总计列!
请各位指点!
,当然你还要用grouping 判断一下把不要汇总的过滤掉
-------------------
Declare @sql varchar(8000)
select @sql='';
select @sql='select departmentid,departmentname,staffid,staffname,monthid'
select @sql= @sql + ' ,isnull( ( select salaryreportitemresult from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid and a.ReportItemName=''' + reportitemname + ''' ) ,0) as ' + reportitemname from CustomerSalaryReportItems
select @sql= @sql + ' , isnull( ( select sum(isnull(salaryreportitemresult,0)) from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid ),0) as 金额小计 '
select @sql=@sql+ ' from #TMPSRTR group by departmentid,departmentname,staffid,staffname,monthid with rollup'
exec(@SQL)
不能对包含聚合或子查询的表达式执行聚合函数。我用的是SQL SERVER 2000
--1、 定义相关变量
--2、生成插入数据的代码
--3、插入数据
if exists( select * from tempdb..sysobjects where name=@dbname )
exec( ' drop table '+ @dbname)
select @sql='';
select @sql='select departmentid,departmentname,staffid,staffname,monthid'
select @sql= @sql + ' ,isnull( ( select salaryreportitemresult from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid and a.ReportItemName=''' + reportitemname + ''' ) ,0) as ' + reportitemname from CustomerSalaryReportItems
select @sql= @sql + ' , isnull( ( select sum(isnull(salaryreportitemresult,0)) from #TMPSRTR a where a.departmentid=#TMPSRTR.departmentid and a.staffid=#TMPSRTR.staffid and a.monthid=#TMPSRTR.monthid ),0) as 金额小计 '
select @sql=@sql+ ' into '+ @dbname +' from #TMPSRTR group by departmentid,departmentname,staffid,staffname,monthid '
select @sql=@sql + ' order by departmentid,staffid '
EXEC(@SQL)
--4聚合(如有不用全局临时表的方法请指点)
select @sql='';
select @sql='select departmentid,case when grouping(departmentname)=1 then ''合计'' else departmentname end as部门名称,staffid,staffname as 姓名'
select @sql= @sql + ' ,sum(' + reportitemname + ') as ' + reportitemname from CustomerSalaryReportItems
select @sql= @sql + ' ,sum(金额小计) as 金额小计 '
select @sql=@sql+ ' from '+ @dbname +' group by departmentid,departmentname,staffid,staffname,monthid '
select @sql=@sql+ 'with rollup having grouping(staffid)=0 and grouping(staffname)=0 and grouping(monthid)=0 '
select @sql=@sql+ 'or grouping(departmentid)=1 or grouping(departmentname)=1 '
exec(@SQL)如果各位有更好的方法请指点