By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE source (
staff VARCHAR(32),
schedule INT,
course VARCHAR(32)
)
INSERT INTO
source
VALUES
('Harry', 0, NULL),
('Harry', 2, 'Music'),
('Harry', 2, 'Maths'),
('Harry', 1, 'Science'),
('Harry', 3, 'French'),
('Greg', 0, NULL),
('Greg', 2, 'English'),
('Greg', 1, 'History'),
('Greg', 3, 'Geography'),
('Greg', 2, 'Civics'),
('Greg', 1, 'Philosophy')
11 rows affected
WITH
ranked AS
(
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY staff, schedule
ORDER BY course
)
AS rank
FROM
source
)
SELECT
staff,
MAX(CASE WHEN schedule = 1 THEN course END) AS period_1,
MAX(CASE WHEN schedule = 2 THEN course END) AS period_2,
MAX(CASE WHEN schedule = 3 THEN course END) AS period_3
FROM
ranked
GROUP BY
staff,
rank
ORDER BY
staff,
rank
| staff | period_1 | period_2 | period_3 |
|---|---|---|---|
| Greg | History | Civics | Geography |
| Greg | Philosophy | English | null |
| Harry | Science | Maths | French |
| Harry | null | Music | null |
Warning: Null value is eliminated by an aggregate or other SET operation.