1、初始数据
select m.DEPARTMENT_ID,m.DEPARTMENT_NAME,level as dept_level
from v_cs_base_department m
start with m.DEPARTMENT_ID = 23981
connect by m.DEPARTMENT_ID = prior m.PARENT_ID
2、中间数据
select
DECODE(dept_level,'2',DEPARTMENT_NAME,NULL) area_name,
DECODE(dept_level,'3',DEPARTMENT_NAME,NULL) company_name
from (select m.DEPARTMENT_ID,m.DEPARTMENT_NAME,level as dept_level
from v_cs_base_department m
start with m.DEPARTMENT_ID = 23981
connect by m.DEPARTMENT_ID = prior m.PARENT_ID)
3、最后数据(如果wm_concat后的结果没有出现clob类型,可以不用to_char()函数进行转换)
select to_char(wmsys.wm_concat(area_name)) area_name,
to_char(wmsys.wm_concat(company_name)) company_name
from (
select
DECODE(dept_level,'2',DEPARTMENT_NAME,NULL) area_name,
DECODE(dept_level,'3',DEPARTMENT_NAME,NULL) company_name
from (select m.DEPARTMENT_ID,m.DEPARTMENT_NAME,level as dept_level
from v_cs_base_department m
start with m.DEPARTMENT_ID = 23981
connect by m.DEPARTMENT_ID = prior m.PARENT_ID))