Want to format data - executed by * Connect By Prior query *
716127May 4 2012 — edited May 4 2012I am executing one connect by prior query
select ID, decode (level_number, 1, supervisor) as lvl1,
decode (level_number, 2, supervisor) as lvl2,
decode (level_number, 3, supervisor) as lvl3,
decode(LEVEL_NUMBER , 2 ,JOB_DESCRIPTION, NULL) AS LEVEL1_TITLE,
decode(LEVEL_NUMBER , 3 ,JOB_DESCRIPTION, NULL) AS LEVEL2_TITLE,
decode(LEVEL_NUMBER , 4 ,JOB_DESCRIPTION, NULL) AS LEVEL3_TITLE,
from (select connect_by_root ID as ID, supervisor, JOB_DESCRIPTION, level as level_number
from TBL_EMPLOYEE start with ID in (select ID from TBL_EMPLOYEE
WHERE EMP_STATUS = 'A'
AND supervisor IS NOT NULL)
CONNECT BY ID = PRIOR SUPERVISOR);
and getting data in below format
*"ID","LVL1","LVL2","LVL3,"LEVEL1_TITLE","LEVEL2_TITLE","LEVEL3_TITLE"*
"A101","B101","","","","",""
"A101","","B102","","Sr Executive","",""
"A101","","","B103","","Manager",""
I want to format this data as given below:
*"ID","LVL1","LVL2","LVL3,"LEVEL1_TITLE","LEVEL2_TITLE","LEVEL3_TITLE"*
"A101","B101","B102","B103","","Sr Executive","Manager"