oracle sys_connect_by_root,CONNECT_BY_ROOT实现行列转换

10g以下版本的ORACLE可以通过DECODE等实现行列转换，[@more@]ORACLE 10g开始新增的CONNECT_BY_ROOT能比较方便地实现行列转换。

例：

有一表ABC,字段信息包括region(地区)，name(国家)，population(人口)。

表中记录信息如下：

region name population

A A1 1000

A A2 5000

B B1 2000

B B2 3000

......

要求用sql查询语句实现以下结果：

A_num B_num ......

6000 5000 ......

实现：

SQL>Create Table ABC (

region Char(10),

Name Char(20),

population Number

);

/

SQL>Insert Into ABC Values('A','A1',1000);

/

SQL>Insert Into ABC Values('A','A2',5000);

/

SQL>Insert Into ABC Values('B','B1',2000);

/

SQL>Insert Into ABC Values('B','B2',3000);

/

SQL>Insert Into ABC Values('C','C1',4000);

/

SQL>Select * From ABC;

/

region name population

A A1 1000

A A2 5000

B B1 2000

B B2 3000

C C1 4000

......

SQL>

SELECT

odr,

replace(path,'_','') path

FROM

(SELECT

1 odr,

Count(*) over()-Rownum idx,

path

FROM

(SELECT

SYS_CONNECT_BY_PATH(content, '_') Path

FROM

(SELECT

num,

num1,

content

FROM

( With t As (Select no,nsum,Rownum num From ( Select Distinct REGION no,Sum(POPULATION) over(Partition By REGION ) nsum From ABC ) ) Select num,num+1 num1,no content From t )

) Start With num=1 Connect By NOCYCLE num = Prior num1

)

)

WHERE idx=0

UNION

SELECT

odr,

replace(path,'_','') path

FROM

(SELECT

2 odr,

Count(*) over()-Rownum idx,

path

FROM

(SELECT

SYS_CONNECT_BY_PATH(content, '_') Path

FROM

(SELECT

num,

num1,

content

FROM

( With t As (Select no,nsum,Rownum num From ( Select Distinct REGION no,Sum(POPULATION) over(Partition By REGION ) nsum From ABC ) ) Select num,num+1 num1,to_char(nsum)||' ' content From t )

) Start With num=1 Connect By NOCYCLE num = Prior num1

)

)

WHERE idx=0;

/

odr path

1 A B C

2 6000 5000 4000

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/174388/viewspace-914475/，如需转载，请注明出处，否则将追究法律责任。