Oracle 21c

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';
CREATE TABLE table_name (col1, col2, col3, col4) AS
SELECT 'YP HEVEA', 'YP HEVEA', 'OTHER CREDITORS',  'L1220001 - OTHER CREDITORS' FROM DUAL UNION ALL
SELECT 'YP HEVEA', 'YP HEVEA', 'PREPAYMENTS', 'A1323004 - PREPAYMENTS OTHERS' FROM DUAL UNION ALL
SELECT 'YP HEVEA', 'YP HEVEA', 'PREPAYMENTS', 'A2349032 - PREPAYMENTS NEW' FROM DUAL;
3 rows affected

SELECT DISTINCT col, lvl
FROM   table_name
       UNPIVOT(
         col FOR lvl IN (
           col1 AS 1,
           col2 AS 2,
           col3 AS 3,
           col4 AS 4
         )
       )


COL
LVL


YP HEVEA
1

YP HEVEA
2

OTHER CREDITORS
3

L1220001 - OTHER CREDITORS
4

PREPAYMENTS
3

A1323004 - PREPAYMENTS OTHERS
4

A2349032 - PREPAYMENTS NEW
4

COL	LVL
YP HEVEA	1
YP HEVEA	2
OTHER CREDITORS	3
L1220001 - OTHER CREDITORS	4
PREPAYMENTS	3
A1323004 - PREPAYMENTS OTHERS	4
A2349032 - PREPAYMENTS NEW	4