Join two query results where one is created from column values

Question

I have a table tables

Table1:

Name|Starttime|Endtime|Project_Number

Frank|   12:00|  16:00|Project1

Frank|   08:00|  16:00|Project2

Andre|   09:00|  16:00|Project4

Andre|   11:00|  16:00|Project5

I try to accomplish to get a table that shows the time worked all together and for each project like this:

Table2:

Name |All|Project1|Project2|Project3|Project4

Andre|12 |4       |8       |Null    |Null

Frank|12 |Null    |Null    |7       |5

I can get the result for all

Select Name, sum(datediff(Minute, Starttime, Endtime)) from Table1

group by Name, sum(datediff(Minute, Starttime, Endtime))

and I can accomplish that for

Table2

as well (with a group over Project as well) but I just do not get the result I want. I already tried

UNION

but that just maps the tables.

Anyone who can help me get this done?

Answer 1

Do the datediff calculation in a derived table. (To keep the code pretty!)

Then use case expressions to do conditional aggregation:

select Name,
       sum(ts),
       sum(case when Project_Number = 'Project1' then ts end) as Project1,
       sum(case when Project_Number = 'Project2' then ts end) as Project2,
       sum(case when Project_Number = 'Project3' then ts end) as Project3,
       sum(case when Project_Number = 'Project4' then ts end) as Project4
from
(
    select Name, datediff(Minute, Starttime, Endtime) as ts, Project_Number
    from Table1
)
group by Name

You can also skip the derived table:

select Name,
       sum(datediff(Minute, Starttime, Endtime)),
       sum(case when Project_Number = 'Project1' then datediff(Minute, Starttime, Endtime) end) as Project1,
       sum(case when Project_Number = 'Project2' then datediff(Minute, Starttime, Endtime) end) as Project2,
       sum(case when Project_Number = 'Project3' then datediff(Minute, Starttime, Endtime) end) as Project3,
       sum(case when Project_Number = 'Project4' then datediff(Minute, Starttime, Endtime) end) as Project4
from Table1
group by Name

Join two query results where one is created from column values

Sign up