Oracle 18c

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

BANNER	BANNER_FULL	BANNER_LEGACY	CON_ID
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production	Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0	Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production	0

with
  T as (
    select '543318' as ACCOUNT_NUMBER,
           '543318' as SUBSCRIBER_ID,
           'Active' as status,
           '20021' as id1, '20021' as id2, '20021' as id3 from dual
    union all
    select '9900' as ACCOUNT_NUMBER,
           '963421' as SUBSCRIBER_ID,
           'Active' as status,
           '20021' as id1, '11' as id2, '41' as id3 from dual
    union all
    select '7089' as ACCOUNT_NUMBER,
           '673456' as SUBSCRIBER_ID,
           'Active' as status,
           '11' as id1, '0' as id2, '0' as id3 from dual
    union all
    select '8909' as ACCOUNT_NUMBER,
           '785643' as SUBSCRIBER_ID,
           'Active' as status,
           '20021' as id1, '43' as id2, '0' as id3 from dual
    union all
    select '17042' as ACCOUNT_NUMBER,
           '124816' as SUBSCRIBER_ID,
           'Active' as status,
           '0' as id1, '0' as id2, '0' as id3 from dual
  )
select
  T.ACCOUNT_NUMBER,
  T.SUBSCRIBER_ID,
  T.status,
  L.id1
from T
outer apply (select T.id1 from dual where T.id1 != '0' union
             select T.id2 from dual where T.id2 != '0' union
             select T.id3 from dual where T.id3 != '0') L;

ACCOUNT_NUMBER	SUBSCRIBER_ID	STATUS	ID1
543318	543318	Active	20021
9900	963421	Active	11
9900	963421	Active	20021
9900	963421	Active	41
7089	673456	Active	11
8909	785643	Active	20021
8909	785643	Active	43
17042	124816	Active	null