根据楼主需求,试写了下,看看是否满足要求 --建存储过程如下 create proc P_test @dwid varchar(4), @beginDate datetime, @endDate datetime as declare @sqlpart varchar(1000) declare @sql varchar(2000)select @sqlpart=stuff(( select ','+'sum(case when reportdate= '''+convert(varchar(20),reportdate,23)+''' then totlerepair else null end )' +' ['+ cast(datepart(year,reportdate) as varchar(4))+'年'+cast(datepart(MONTH,reportdate) as varchar(4))+'月]' from basicdate group by reportdate order by reportdate for xml path('') ),1,1,'') exec('select '+@sqlpart+' from basicdate where reportdate between '''+@beginDate+''' and '''+@endDate+''' and dwid='''+@dwid+''' ')/* 执行存储过程 exec P_test 1,'20130101 00:00:00','20140501 02:15:20' */
select SS.Subject as 字段 ,sum(case when MONTH(ss.reportdate) = 1 then ss.Result else 0 end) as '1月' ,sum(case when MONTH(ss.reportdate) = 2 then ss.Result else 0 end) as '2月' ,sum(case when MONTH(ss.reportdate) = 3 then ss.Result else 0 end) as '3月' ,sum(case when MONTH(ss.reportdate) = 4 then ss.Result else 0 end) as '4月' ,sum(case when MONTH(ss.reportdate) = 5 then ss.Result else 0 end) as '5月' ,sum(case when MONTH(ss.reportdate) = 6 then ss.Result else 0 end) as '6月' ,sum(case when MONTH(ss.reportdate) = 7 then ss.Result else 0 end) as '7月' ,sum(case when MONTH(ss.reportdate) = 8 then ss.Result else 0 end) as '8月' ,sum(case when MONTH(ss.reportdate) = 9 then ss.Result else 0 end) as '9月' ,sum(case when MONTH(ss.reportdate) = 10 then ss.Result else 0 end) as '10月' ,sum(case when MONTH(ss.reportdate) = 11 then ss.Result else 0 end) as '11月' ,sum(case when MONTH(ss.reportdate) = 12 then ss.Result else 0 end) as '12月' FROM( select 公司名称,reportdate,Subject,SUM(Result) AS 'Result' from ( select dwname as 公司名称 , reportdate ,Subject = '工时收入' , Result = hourlypay FROM View_basicdate union all select dwname as 公司名称 , reportdate ,Subject = '配件收入' , Result = repairpay FROM View_basicdate union all select dwname as 公司名称 , reportdate ,Subject = '销售收入' , Result = salespay FROM View_basicdate ) T WHERE YEAR(reportdate)='2014' --AND MONTH(reportdate)='4' GROUP BY 公司名称,reportdate,Subject ) ss group by ss.Subject一通瞎写,能优化不?
--建存储过程如下
create proc P_test
@dwid varchar(4),
@beginDate datetime,
@endDate datetime
as
declare @sqlpart varchar(1000)
declare @sql varchar(2000)select
@sqlpart=stuff((
select
','+'sum(case when reportdate= '''+convert(varchar(20),reportdate,23)+''' then totlerepair else null end )' +' ['+ cast(datepart(year,reportdate) as varchar(4))+'年'+cast(datepart(MONTH,reportdate) as varchar(4))+'月]'
from basicdate
group by reportdate
order by reportdate
for xml path('')
),1,1,'')
exec('select '+@sqlpart+' from basicdate where reportdate between '''+@beginDate+''' and '''+@endDate+''' and dwid='''+@dwid+''' ')/*
执行存储过程
exec P_test 1,'20130101 00:00:00','20140501 02:15:20'
*/
,sum(case when MONTH(ss.reportdate) = 1 then ss.Result else 0 end) as '1月'
,sum(case when MONTH(ss.reportdate) = 2 then ss.Result else 0 end) as '2月'
,sum(case when MONTH(ss.reportdate) = 3 then ss.Result else 0 end) as '3月'
,sum(case when MONTH(ss.reportdate) = 4 then ss.Result else 0 end) as '4月'
,sum(case when MONTH(ss.reportdate) = 5 then ss.Result else 0 end) as '5月'
,sum(case when MONTH(ss.reportdate) = 6 then ss.Result else 0 end) as '6月'
,sum(case when MONTH(ss.reportdate) = 7 then ss.Result else 0 end) as '7月'
,sum(case when MONTH(ss.reportdate) = 8 then ss.Result else 0 end) as '8月'
,sum(case when MONTH(ss.reportdate) = 9 then ss.Result else 0 end) as '9月'
,sum(case when MONTH(ss.reportdate) = 10 then ss.Result else 0 end) as '10月'
,sum(case when MONTH(ss.reportdate) = 11 then ss.Result else 0 end) as '11月'
,sum(case when MONTH(ss.reportdate) = 12 then ss.Result else 0 end) as '12月' FROM(
select 公司名称,reportdate,Subject,SUM(Result) AS 'Result' from
(
select dwname as 公司名称 , reportdate ,Subject = '工时收入' , Result = hourlypay FROM View_basicdate
union all
select dwname as 公司名称 , reportdate ,Subject = '配件收入' , Result = repairpay FROM View_basicdate
union all
select dwname as 公司名称 , reportdate ,Subject = '销售收入' , Result = salespay FROM View_basicdate
) T
WHERE YEAR(reportdate)='2014' --AND MONTH(reportdate)='4'
GROUP BY 公司名称,reportdate,Subject
) ss group by ss.Subject一通瞎写,能优化不?