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
![](https://i-blog.csdnimg.cn/blog_migrate/2f77e2840728a37a803e793558f8a8b2.png)
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)
![](https://i-blog.csdnimg.cn/blog_migrate/98e47ba121cbf43c2841148328f39e44.png)
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))
![](https://i-blog.csdnimg.cn/blog_migrate/74cc8cfec772dc118ced09dea6b5a723.png)