how to combine two different tables into one

Question

I have a table with below structure

INSERT INTO reservation(rid, WHEEL_CHAIR, blind) VALUES (1,8,9)
INSERT INTO reservation(rid, WHEEL_CHAIR, blind) VALUES (2,11,12)
    
SELECT id,
       WHEEL_CHAIR,
       blind
FROM   reservation

1	8	9
2	11	12

This gives a count of people who need wheel chair(8) and count of blind people (9) and I have a disability table where the details of the disability is explained

INSERT INTO disability(hid, code, DESCRIPTION) VALUES (5,'aa', 'wheel chair')
INSERT INTO disability(hid, code, DESCRIPTION) VALUES (7,'bl', 'blind')

I want to club the above 2 tables and need to have a new table with the data

insert into newTable( newID,hid,count) values (1,5,8) 
insert into newTable( newID,hid,count) values (1,7,9)
insert into newTable( newID,hid,count) values (2,5,11) 
insert into newTable( newID,hid,count) values (2,7,12)

here 5 and 7 are the disability ids and 8,9,11,12 are the count of the blind people or the count of people who need a wheel chair.

Answer 1

Unpivot the reservation table and then INNER JOIN it to the disabilities table:

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)

Which, for the sample data:

CREATE TABLE reservation(rid, WHEEL_CHAIR, blind) AS
  SELECT 1,8,9 FROM DUAL UNION ALL
  SELECT 2,11,12 FROM DUAL;

CREATE TABLE disability(hid, code, DESCRIPTION) AS
  SELECT 5, 'aa', 'wheel chair' FROM DUAL UNION ALL
  SELECT 7, 'bl', 'blind' FROM DUAL

Outputs:

NEWID	HID	CNT
1	5	8
1	7	9
2	5	11
2	7	12

fiddle