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?
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