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