换了一种,不用判断好了!

WITH a 
AS 
(
SELECT '340000' a, '安徽省' b FROM dual
UNION ALL
SELECT '341100', '安庆市' FROM dual
UNION ALL
SELECT '341101', 'MMM' FROM dual
UNION ALL
SELECT '341102', 'AAAA' FROM dual
UNION ALL
SELECT '110000', '北京市' FROM dual
UNION ALL
SELECT '111200', '东城区' FROM dual
)
SELECT substr(max(sys_connect_by_path(b,',')),2)
FROM 
(
       select a,b,(CASE WHEN INSTR(a,'0000')>0 THEN 1 WHEN INSTR(a,'00')>0 THEN 2 ELSE 3 END) rn ,SUBSTR(a,1,2) aa from a
)
connect by  prior rn=rn-1  AND PRIOR aa=aa
START WITH rn=1
group by a

--考虑到另一种情况...改了一下!请TEST.
--result:
北京市
北京市,东城区
安徽省
安徽省,安庆市
安徽省,安庆市,MMM
安徽省,安庆市,AAAA

------解决方案--------------------

select case when substr(a.codeid,-4)='0000' then  
                a.name||';'
						when substr(a.codeid,-2)='00' then  
							    b.name||';'||a.name ||';'
						when substr(a.codeid,-2)<>'00' then  
							    b.name||';'||c.name||';'||a.name ||';'
								 end
from table_a a,
    table_a b,  --省
		  table_a c  --市
where  b.codeid=substr(a.codeid,1,2)||'0000' and c.codeid=substr(a.codeid,1,4)||'00'
and b.codeid=substr(c.codeid,1,2)||'0000'
order by a.codeid