By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (Name, DT, Award) AS
SELECT 'Roger', DATE '2024-06-01', 'A1' FROM DUAL UNION ALL
SELECT 'Roger', DATE '2024-06-01', 'A2' FROM DUAL UNION ALL
SELECT 'Roger', DATE '2024-07-01', 'A3' FROM DUAL UNION ALL
SELECT 'Alice', DATE '2024-06-01', 'A4' FROM DUAL UNION ALL
SELECT 'Alice', DATE '2024-07-01', 'A5' FROM DUAL UNION ALL
SELECT 'Aidan', DATE '2024-01-01', 'A6' FROM DUAL;
6 rows affected
SELECT *
FROM table_name
PIVOT(
LISTAGG(award, ',')
FOR dt IN (
DATE '2024-01-01' AS "2024-01-01",
DATE '2024-06-01' AS "2024-06-01",
DATE '2024-07-01' AS "2024-07-01"
)
)
| NAME | 2024-01-01 | 2024-06-01 | 2024-07-01 |
|---|---|---|---|
| Aidan | A6 | null | null |
| Alice | null | A4 | A5 |
| Roger | null | A1,A2 | A3 |
SELECT *
FROM (
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY name, dt ORDER BY award) AS rn
FROM table_name t
)
PIVOT(
MAX(award)
FOR (dt, rn) IN (
(DATE '2024-01-01', 1) AS "2024-01-01_1",
(DATE '2024-06-01', 1) AS "2024-06-01_1",
(DATE '2024-06-01', 2) AS "2024-06-01_2",
(DATE '2024-07-01', 1) AS "2024-07-01_1"
)
)
| NAME | 2024-01-01_1 | 2024-06-01_1 | 2024-06-01_2 | 2024-07-01_1 |
|---|---|---|---|---|
| Aidan | A6 | null | null | null |
| Alice | null | A4 | null | A5 |
| Roger | null | A1 | A2 | A3 |