Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

DROP TABLE R;

create table r (
Rtype varchar2(20),
name varchar2(20),
child varchar2(20)
);

--AREA 1 AND 2,EACH HAVE TWO PROVINCES
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('AREA','SOUTH','PROVINCE1');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('AREA','SOUTH','PROVINCE2');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('AREA','NORTH','PROVINCE3');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('AREA','NORTH','PROVINCE4');
--PROVINCE 1-4,EACH HAVE TWO COMPANIES
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('PROVINCE','PROVINCE1','COMPANY1');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('PROVINCE','PROVINCE1','COMPANY2');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('PROVINCE','PROVINCE2','COMPANY3');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('PROVINCE','PROVINCE2','COMPANY4');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('PROVINCE','PROVINCE3','COMPANY5');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('PROVINCE','PROVINCE3','COMPANY6');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('PROVINCE','PROVINCE4','COMPANY7');
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('PROVINCE','PROVINCE4','COMPANY8');
8 COMPANIES IS THE LEAF OF THE TREE
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('COMPANY','COMPANY1',NULL);
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('COMPANY','COMPANY2',NULL);
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('COMPANY','COMPANY3',NULL);
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('COMPANY','COMPANY4',NULL);
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('COMPANY','COMPANY5',NULL);
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('COMPANY','COMPANY6',NULL);
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('COMPANY','COMPANY7',NULL);
INSERT INTO R (RTYPE,NAME, CHILD) VALUES ('COMPANY','COMPANY8',NULL);

COMMIT;

SQL> column cname format a10
SQL> column rtype format a10
SQL> column prov format a10
SQL> column area format a10
SQL> column child format a10

SQL> SELECT * FROM R;

RTYPE NAME CHILD
---------- ---------- ----------
AREA SOUTH PROVINCE1
AREA SOUTH PROVINCE2
AREA NORTH PROVINCE3
AREA NORTH PROVINCE4
PROVINCE PROVINCE1 COMPANY1
PROVINCE PROVINCE1 COMPANY2
PROVINCE PROVINCE2 COMPANY3
PROVINCE PROVINCE2 COMPANY4
PROVINCE PROVINCE3 COMPANY5
PROVINCE PROVINCE3 COMPANY6
PROVINCE PROVINCE4 COMPANY7

RTYPE NAME CHILD
---------- ---------- ----------
PROVINCE PROVINCE4 COMPANY8
COMPANY COMPANY1
COMPANY COMPANY2
COMPANY COMPANY3
COMPANY COMPANY4
COMPANY COMPANY5
COMPANY COMPANY6
COMPANY COMPANY7
COMPANY COMPANY8

TO CHANGE R TO ANOTHER FORMAT ,I CREATE A VIEW CV AS

CREATE OR REPLACE VIEW CV AS
select A.pth CNAME,
max(decode(A.rtype,'PROVINCE',A.NAME,null)) PROV ,
max(decode(A.rtype,'AREA',A.NAME,null)) area from
(select R.*,
nvl(substr(SYS_CONNECT_BY_PATH(R.NAME, '.'),2,instr(SYS_CONNECT_BY_PATH(R.NAME, '.'),'.',1,2)-2),R.NAME) Pth
from r CONNECT BY prior R.NAME= R.CHILD start with R.CHILD is null) A group by A.pth ;

SQL> SELECT * FROM CV;

CNAME PROV AREA
---------- ---------- ----------
COMPANY1 PROVINCE1 SOUTH
COMPANY2 PROVINCE1 SOUTH
COMPANY3 PROVINCE2 SOUTH
COMPANY4 PROVINCE2 SOUTH
COMPANY5 PROVINCE3 NORTH
COMPANY6 PROVINCE3 NORTH
COMPANY7 PROVINCE4 NORTH
COMPANY8 PROVINCE4 NORTH

THE RESULTS ARE JUST WHAT I NEED.BUT ,

DESC CV
CNAME VARCHAR2(4000)
PROV VARCHAR2(20)
AREA VARCHAR2(20)

the length of cname make me upset,can i have a way to change it to the nomal length?(It should be varchar2(20))