Joining together 2 tables while joining one table twice with different filters

Question

I have collected data in 2 SQL Server tables:

AREAS

area
a
b

SHIFTS

personal_id	date	shifttime	area
12	2012-01-10	early	a
13	2012-01-10	early	a
14	2012-01-10	late	a
15	2012-01-10	early	b

I want to join them together in a query like this:

area	date	early	late
a	2012-01-10	12	14
a	2012-01-10	13
b	2012-01-10	15

I have tried something like:

SELECT
    AREAS.area, early.date, early.personal_id as early, 
    late, personal_id as late
FROM 
    AREAS
LEFT JOIN 
    SHIFTS AS early ON AREAS.area = early.area
LEFT JOIN 
    SHIFTS AS late ON AREAS.area = late.area
WHERE
    early.shifttime = 'early' 
    AND late.shifttime = 'late' 
    AND date = '2012-01-10'

but that outputs:

area	date	early	late
a	2012-01-10	12	14
a	2012-01-10	13	14
b	2012-01-10	15

Is there any other way to join or do I have to save my data differently in SQL Server?

Answer 1

There are multiple personal_id under the same area, date & shift and you wanted to show it under different row. You can use row_number() to generate a running number for each of the personal_id.

row_number() over (partition by a.area, s.date, s.shifttime 
                       order by s.personal_id)

From there you can use the PIVOT query

select area, date, early, late
from   (
           select a.area, s.date, s.shifttime, s.personal_id,
                  rn = row_number() over (partition by a.area, s.date, s.shifttime 
                                              order by s.personal_id)
           from   AREAS a
                  left join SHIFTS s on a.area = s.area
       ) d
       pivot
       (
           max (personal_id)
           for shifttime in ([early], [late])
       ) p

or the conditional case expression way

select d.area, d.date, 
       early = max(case when shifttime = 'early' then personal_id end), 
       late  = max(case when shifttime = 'late'  then personal_id end)
from   (
           select a.area, s.date, s.shifttime, s.personal_id,
                  rn = row_number() over (partition by a.area, s.date, s.shifttime 
                                              order by s.personal_id)
           from   AREAS a
                  left join SHIFTS s on a.area = s.area
       ) d
group by d.area, d.date, rn