By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1
("person" TEXT(8), "job_a" TEXT(5), "job_b" TEXT(5))
;
✓
INSERT INTO Table1
("person", "job_a", "job_b")
VALUES
('Person 1', 'Job 1', 'Job 3')
;
✓
INSERT INTO Table1
("person", "job_a", "job_b")
VALUES
('Person 2', 'Job 3', 'Job 2')
;
✓
INSERT INTO Table1
("person", "job_a", "job_b")
VALUES
('Person 3', 'Job 4', 'Job 1')
;
✓
SELECT *
FROM Table1;
| person | job_a | job_b |
|---|---|---|
| Person 1 | Job 1 | Job 3 |
| Person 2 | Job 3 | Job 2 |
| Person 3 | Job 4 | Job 1 |
WITH cte(num) AS
(
VALUES
(1),
(2)
)
SELECT
CASE num
WHEN 1 THEN job_a
WHEN 2 THEN job_b
END AS job,
MIN(person) AS person_a,
CASE
WHEN MIN(person) <> MAX(person)
THEN MAX(person)
END AS person_b
FROM Table1
CROSS JOIN cte
GROUP BY job;
| job | person_a | person_b |
|---|---|---|
| Job 1 | Person 1 | Person 3 |
| Job 2 | Person 2 | null |
| Job 3 | Person 1 | Person 2 |
| Job 4 | Person 3 | null |