alter procedure sp_rsydTot @month varchar(7) as
select employeeNO,(case when worktype not like '管理人员%' then '员工' else '管理人员' end) as worktype into #t1 from hr2014..personal
union all
select employeeNO,(case when worktype not like '管理人员%' then '员工' else '管理人员' end) as worktype from hr2014..personal4
select a.*,b.worktype as worktype into #t2 from hr2014..T_upDate as a left join #t1 as b on a.employeeNO=b.employeeNO where convert(varchar(7),[date],121)=@month
select
sum(case when OldDept=NewDept and OldTeam=NewTeam and OldGroup=newGroup and oldGroup1=NewGroup1 then 1 else 0 end) bbnbdd,
sum(case when OldDept=NewDept and OldTeam=NewTeam and OldGroup=newGroup and oldGroup1<>NewGroup1 then 1 else 0 end) bbdd,
sum(case when OldDept=NewDept and OldTeam=NewTeam and OldGroup<>newGroup then 1 else 0 end) xbdd,
sum(case when (OldDept=NewDept and OldTeam<>NewTeam) or (OldDept<>NewDept) then 1 else 0 end) kbdd,
sum(case when worktype='管理人员' then 1 else 0 end) as glry,
sum(case when worktype='员工' then 1 else 0 end) as yg,
sum(case when OldDept=NewDept and OldTeam=NewTeam and OldGroup=newGroup and oldGroup1=NewGroup1 and UpdateReason='昇格' then 1 else 0 end) sgbbnbdd,
sum(case when OldDept=NewDept and OldTeam=NewTeam and OldGroup=newGroup and oldGroup1<>NewGroup1 and UpdateReason='昇格' then 1 else 0 end) sgbbdd,
sum(case when OldDept=NewDept and OldTeam=NewTeam and OldGroup<>newGroup and UpdateReason='昇格' then 1 else 0 end) sgxbdd,
sum(case when ((OldDept=NewDept and OldTeam<>NewTeam) or (OldDept<>NewDept)) and UpdateReason='昇格' then 1 else 0 end) sgkbdd,
sum(case when OldDept=NewDept and OldTeam=NewTeam and OldGroup=newGroup and oldGroup1<>NewGroup1 and UpdateReason='昇格' then 1 else 0 end) sgbbdd,
sum(case when UpdateReason not like '补缺%' then 1 else 0 end) ybgzxy,
sum(case when UpdateReason like '补缺%' then 1 else 0 end) bq,
sum(case when OldTeam='成型科' and UpdateReason='昇格' then 1 else 0 end) sgcx,
sum(case when OldTeam='品证科' and UpdateReason='昇格' then 1 else 0 end) sgpz,
sum(case when OldTeam='生管科' and UpdateReason='昇格' then 1 else 0 end) sgsg,
sum(case when OldTeam like '后工程%' and UpdateReason='昇格' then 1 else 0 end) sghgc,
sum(case when OldTeam='技术科' and UpdateReason='昇格' then 1 else 0 end) sgzs,
sum(case when OldTeam='总务科' and UpdateReason='昇格' then 1 else 0 end) sgzw,
sum(case when OldTeam='设备科' and UpdateReason='昇格' then 1 else 0 end) sgsb,
sum(case when OldTeam='OA滚轮科' and UpdateReason='昇格' then 1 else 0 end) sgOA,
sum(case when OldTeam not in ('OA滚轮科','设备科','总务科','技术科','生管科','品证科','成型科') and OldTeam not like '后工程%' and UpdateReason='昇格' then 1 else 0 end) sgqt,
sum(case when UpdateReason='昇格' then 1 else 0 end) sg,
sum(case when OldTeam='OA滚轮科' then 1 else 0 end) OA,
sum(case when OldTeam='成型科' then 1 else 0 end) cx,
sum(case when OldTeam like '后工程%' then 1 else 0 end) hgc,
sum(case when OldTeam not in ('OA滚轮科','成型科') and OldTeam not like '后工程%' then 1 else 0 end) qt,
count(*) as '小计'
from #t2
求把这个存储过程的列转换成行。
------解决方案--------------------
每个SUM的地方都UNION ALL起来就OK了 你的语句太长了。