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';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE reservation(rid, WHEEL_CHAIR, blind) AS
SELECT 1,8,9 FROM DUAL UNION ALL
SELECT 2,11,12 FROM DUAL;
2 rows affected
CREATE TABLE disability(hid, code, DESCRIPTION) AS
SELECT 5, 'aa', 'wheel chair' FROM DUAL UNION ALL
SELECT 7, 'bl', 'blind' FROM DUAL
2 rows affected
SELECT rid AS newId, hid, cnt
FROM reservation
UNPIVOT ( cnt FOR description IN (wheel_chair AS 'wheel chair', blind AS 'blind')) r
INNER JOIN disability d
ON (d.description = r.description)
| NEWID | HID | CNT |
|---|---|---|
| 1 | 5 | 8 |
| 1 | 7 | 9 |
| 2 | 5 | 11 |
| 2 | 7 | 12 |