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 (firstname, lastname, Dt) AS
SELECT 'Hans', 'Meier', DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 'Hans', 'Meier', DATE '1970-01-02' FROM DUAL UNION ALL
SELECT 'Hans', 'Meier', DATE '1970-01-03' FROM DUAL UNION ALL
SELECT 'Klaus', 'Schmitt', DATE '1971-01-01' FROM DUAL UNION ALL
SELECT 'Klaus', 'Schmitt', DATE '1971-01-02' FROM DUAL UNION ALL
SELECT 'Klaus', 'Schmitt', DATE '1971-01-03' FROM DUAL
6 rows affected
SELECT *
FROM (SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY firstname, lastname ORDER BY dt) AS rn
FROM table_name t)
PIVOT(
MAX(dt)
FOR rn IN (
1 AS dt1,
2 AS dt2,
3 AS dt3
)
);
| FIRSTNAME | LASTNAME | DT1 | DT2 | DT3 |
|---|---|---|---|---|
| Hans | Meier | 1970-01-01 00:00:00 | 1970-01-02 00:00:00 | 1970-01-03 00:00:00 |
| Klaus | Schmitt | 1971-01-01 00:00:00 | 1971-01-02 00:00:00 | 1971-01-03 00:00:00 |