DROP TABLE #TEMP;

CREATE TABLE #TEMP (ID INT , PARENT_ID INT , NAME NVARCHAR(500) , EMP_NAME NVARCHAR(500));

INSERT INTO #TEMP VALUES (1 , NULL	, 'LEVEL1' , 'NAME_1' );
INSERT INTO #TEMP VALUES (2 , 1		, 'LEVEL2' , 'NAME_2' );
INSERT INTO #TEMP VALUES (3 , 2		, 'LEVEL3' , 'NAME_2' );
INSERT INTO #TEMP VALUES (4 , 3		, 'LEVEL4' , 'NAME_2' );
INSERT INTO #TEMP VALUES (5 , 4		, 'LEVEL5' , 'NAME_3' );
INSERT INTO #TEMP VALUES (6 , 4		, 'LEVEL5' , 'NAME_3' );
INSERT INTO #TEMP VALUES (7 , 4		, 'LEVEL5' , 'NAME_3' );
INSERT INTO #TEMP VALUES (8 , 5		, 'LEVEL6' , 'NAME_4' );



WITH MAIN(ID , NAME , EMP_NAME) AS 
(
SELECT  T1.ID  , T1.NAME , T1.EMP_NAME  FROM #TEMP T1  
UNION ALL
SELECT  T2.ID  , T2.NAME , T2.EMP_NAME FROM #TEMP T2 , MAIN M
WHERE M.ID = T2.PARENT_ID 
)

SELECT  EMP_NAME , STRING_AGG(NAME , CHAR(44)) LEVELS FROM MAIN WHERE EMP_NAME = 'NAME_4' GROUP BY EMP_NAME;

DROP TABLE TEMP;

CREATE TABLE TEMP (ID INT , PARENT_ID INT , LEVELS VARCHAR2(500) , EMP_NAME VARCHAR2(500));

BEGIN
INSERT INTO TEMP VALUES (1 , NULL    , 'LEVEL1' , 'NAME_1' );
INSERT INTO TEMP VALUES (2 , 1        , 'LEVEL2' , 'NAME_2' );
INSERT INTO TEMP VALUES (3 , 2        , 'LEVEL3' , 'NAME_2' );
INSERT INTO TEMP VALUES (4 , 3        , 'LEVEL4' , 'NAME_2' );
INSERT INTO TEMP VALUES (5 , 4        , 'LEVEL5' , 'NAME_3' );
INSERT INTO TEMP VALUES (6 , 4        , 'LEVEL5' , 'NAME_3' );
INSERT INTO TEMP VALUES (7 , 4        , 'LEVEL5' , 'NAME_3' );
INSERT INTO TEMP VALUES (8 , 5        , 'LEVEL6' , 'NAME_4' );
END;

-----------

    SELECT EMP_NAME,
          (SELECT LISTAGG (T.LEVELS, ',') WITHIN GROUP (ORDER BY T.LEVELS)  
           FROM TEMP T 
           START WITH T.ID = T1.ID  
           CONNECT BY T.ID = PRIOR T.PARENT_ID
           )  AS LEVELS    
     FROM TEMP T1 
 WHERE EMP_NAME = 'NAME_4';

WITH MAIN(ID , NAME , EMP_NAME) AS 
(
SELECT  T1.ID  , cast(T1.NAME as varchar(max)) as NAME, T1.EMP_NAME  FROM #TEMP T1  where T1.PARENT_ID is null 
UNION ALL
SELECT  T2.ID  , cast(M.Name+','+T2.NAME as varchar(max)) , T2.EMP_NAME FROM #TEMP T2 inner join MAIN M on M.ID = T2.PARENT_ID 
)
select * from MAIN WHERE EMP_NAME = 'NAME_4'

ID          NAME                                                  EMP_NAME
----------- ------------------------------------------------------------------------
8           LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6             NAME_4