如何在透视中使用案例

Question

我想按部门和日期创建工作时间的透视。 我有一个像 sqlselect department,sum(workinghours),date group by department,date.然后我写一个透视sql

select * from(
select department,sum(workinghours) as hours,date group by department,date
)as RC
PIVOT
(
sum(hours) for date 
)as P

但这返回了很多空值。 所以我将透视更新为

select * from(
select department,sum(workinghours) as hours,date group by department,date
)as RC
PIVOT
(
(case when sum(hours) is null then 0 else sum(hours) end) for date 
)as P

然后它有错误。 我不明白为什么，任何人都可以帮忙？谢谢。

Answer 1

您需要将null替换为sum中的一些特定值。由于我替换了 0 而不是null：

create table #t
(
Id int identity (1,1),
Department varchar (100),
WorkingHours int  ,
Date datetime
)
Insert into #t (Department,WorkingHours,Date)
Select '1','10',GETDATE ()-1
Insert into #t (Department,WorkingHours,Date)
Select '1','20',GETDATE ()-1
Insert into #t (Department,WorkingHours,Date)
Select '1',null,GETDATE ()
select * from #t
select * from(
select department,sum(ISNULL(workinghours,0)) as hours,
cast (Year(date) as varchar) date 
from #t
group by department,date
)as RC
PIVOT
(
sum(hours ) for date  IN ([2018])
)as P

Answer 2

在总和内时尝试以下大小写：

select * from(
select department,sum(workinghours) as hours,date group by department,date
)as RC
PIVOT
(
sum(case when hours is null then 0 else hours end) for date 
)as P