如何在pivot中使用case

I want to create a pivot of working hour by department and date. I had a sql like select department,sum(workinghours),date group by department,date. then I write a pivot sql

我想按部门和日期创建工作时间的支点。我有一个像选择部门,总和(工作时间),按部门的日期组,日期的SQL。然后我写了一个pivot sql

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

but this return lots of null value. so I update the pivot to

但是这会返回很多空值。所以我更新枢轴

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

then it has error. I don't understand why, anyone can help?Thx.

那么它有错误。我不明白为什么,任何人都可以帮忙吗?Thx。

2 个解决方案

#1

You need to replace your null with some specific values in sum. As I have replaced 0 instead of null:

您需要将总和中的某些特定值替换为null。因为我已经替换了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

#2

Try below with case when inside sum :

在内部总和时尝试以下情况:

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

#1