Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TRAN (
    C1 VARCHAR2(50) NOT NULL,
    C2 VARCHAR2(50),
    C3 VARCHAR2(50),
    C4 VARCHAR2(50),
    C5 VARCHAR2(50),
    C6 VARCHAR2(50),
    C7 VARCHAR2(50),
    C8 VARCHAR2(50),
    C9 VARCHAR2(50)
);
CREATE TABLE ADDR (
    C1 VARCHAR2(50) NOT NULL,
    C2 VARCHAR2(50),
    BRANCH VARCHAR2(50),
    ADDR VARCHAR2(50)
);
BEGIN
insert into TRAN (C1, C2, C3, C4, C5, C6, C7, C8, C9) values ('A111', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1', 'Q1');
insert into TRAN (C1, C2, C3, C4, C5, C6, C7, C8, C9) values ('A222', 'Q2', 'Q2', 'Q2', null, null, null, null, 'Q2');
insert into TRAN (C1, C2, C3, C4, C5, C6, C7, C8, C9) values ('A333', 'Q3', 'Q3', 'Q3', 'Q3', 'Q3', 'Q3', 'Q3', 'Q3');
insert into TRAN (C1, C2, C3, C4, C5, C6, C7, C8, C9) values ('A444', null, null, null, null, 'Q4', 'Q4', 'Q4', 'Q4');

insert into ADDR (C1, C2, BRANCH, ADDR) values ('A111', 'Q1', 'CHN', 'INDIA');
insert into ADDR (C1, C2, BRANCH, ADDR) values ('A222', 'Q2','BLR', 'USA');
insert into ADDR (C1, C2, BRANCH, ADDR) values ('A444', 'Q4', 'HYD', 'UK');
END;
/
1 rows affected

SELECT t.c1,
       MAX(CASE WHEN a.c2 = t.c2 THEN a.branch END) AS branch1,
       MAX(CASE WHEN a.c2 = t.c2 THEN a.addr END) AS addr1,
       MAX(CASE WHEN a.c2 = t.c3 THEN a.branch END) AS branch2,
       MAX(CASE WHEN a.c2 = t.c3 THEN a.addr END) AS addr2,
       MAX(CASE WHEN a.c2 = t.c4 THEN a.branch END) AS branch3,
       MAX(CASE WHEN a.c2 = t.c4 THEN a.addr END) AS addr3,
       MAX(CASE WHEN a.c2 = t.c5 THEN a.branch END) AS branch4,
       MAX(CASE WHEN a.c2 = t.c5 THEN a.addr END) AS addr4,
       MAX(CASE WHEN a.c2 = t.c6 THEN a.branch END) AS branch5,
       MAX(CASE WHEN a.c2 = t.c6 THEN a.addr END) AS addr5,
       MAX(CASE WHEN a.c2 = t.c7 THEN a.branch END) AS branch6,
       MAX(CASE WHEN a.c2 = t.c7 THEN a.addr END) AS addr6,
       MAX(CASE WHEN a.c2 = t.c8 THEN a.branch END) AS branch7,
       MAX(CASE WHEN a.c2 = t.c8 THEN a.addr END) AS addr7,
       MAX(CASE WHEN a.c2 = t.c9 THEN a.branch END) AS branch8,
       MAX(CASE WHEN a.c2 = t.c9 THEN a.addr END) AS addr8
FROM   tran t
       LEFT OUTER JOIN addr a
       ON (   t.c1 = a.c1
          AND a.c2 IN (t.c2, t.c3, t.c4, t.c5, t.c6, t.c7, t.c8, t.c9))
GROUP BY t.c1


C1
BRANCH1
ADDR1
BRANCH2
ADDR2
BRANCH3
ADDR3
BRANCH4
ADDR4
BRANCH5
ADDR5
BRANCH6
ADDR6
BRANCH7
ADDR7
BRANCH8
ADDR8


A444
null
null
null
null
null
null
null
null
HYD
UK
HYD
UK
HYD
UK
HYD
UK

A333
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null

A111
CHN
INDIA
CHN
INDIA
CHN
INDIA
CHN
INDIA
CHN
INDIA
CHN
INDIA
CHN
INDIA
CHN
INDIA

A222
BLR
USA
BLR
USA
BLR
USA
null
null
null
null
null
null
null
null
BLR
USA

C1	BRANCH1	ADDR1	BRANCH2	ADDR2	BRANCH3	ADDR3	BRANCH4	ADDR4	BRANCH5	ADDR5	BRANCH6	ADDR6	BRANCH7	ADDR7	BRANCH8	ADDR8
A444	null	null	null	null	null	null	null	null	HYD	UK	HYD	UK	HYD	UK	HYD	UK
A333	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null	null
A111	CHN	INDIA	CHN	INDIA	CHN	INDIA	CHN	INDIA	CHN	INDIA	CHN	INDIA	CHN	INDIA	CHN	INDIA
A222	BLR	USA	BLR	USA	BLR	USA	null	null	null	null	null	null	null	null	BLR	USA