How to convert single column to multiple columns using the delimiter in oracle

 

SELECT 

REGEXP_SUBSTR (description, '[^|]+', 1, 12)    AS "Data Storage",

REGEXP_SUBSTR (description, '[^|]+', 1, 11)    AS "Hierarchy Type",

REGEXP_SUBSTR (description, '[^|]+', 1, 10)    AS uda,

REGEXP_SUBSTR (description, '[^|]+', 1, 2)    AS "Aggregation (FINRPT)",

REGEXP_SUBSTR (description, '[^|]+', 1, 3)    AS "Aggregation (INITMGR)",

REGEXP_SUBSTR (description, '[^|]+', 1, 4)    AS "Aggregation (OEP_FS)",

REGEXP_SUBSTR (description, '[^|]+', 1, 5)    AS "Alias: Default",

REGEXP_SUBSTR (description, '[^|]+', 1, 6)    AS "Alias: Reporting",

REGEXP_SUBSTR (description, '[^|]+', 1, 7)    AS "Alias: Workforce",

REGEXP_SUBSTR (description, '[^|]+', 1, 8)    AS "Alias: NumDesc",

REGEXP_SUBSTR (description, '[^|]+', 1, 9)    AS "Base Currency",

REGEXP_SUBSTR (description, '[^|]+', 1, 1)    AS parent,

lookup_code Entity

from fnd_lookup_values 

where lookup_type like 'XXUNV_EDM_HEIRARCHY_EPM8' and language = 'US';