竖列转成横列显示

select a, substr(replace(max(sys_connect_by_path(b, ';')), ';', ','), 2) b
from (select a,
b,
(row_number() over(order by a, b) + dense_rank()
over(order by a)) rn,
min(b) over(partition by a) mb
from t)
start with b = mb
connect by rn - 1 = prior rn
group by a

SQL> select * from t order by a;

A B
---------- --------------------
1 01
1 02
1 AA
1 03
2 05
2 BB
3 06
4 07

8 rows selected

SQL>
SQL> select a, substr(replace(max(sys_connect_by_path(b, ';')), ';', ','), 2) b
2 from (select a,
3 b,
4 (row_number() over(order by a, b) + dense_rank()
5 over(order by a)) rn,
6 min(b) over(partition by a) mb
7 from t)
8 start with b = mb
9 connect by rn - 1 = prior rn
10 group by a
11 /

A B
1 01,02,03,AA
2 05,BB
4 07
3 06

转自，做了列之间加上逗号http://sdt.sumida.com.cn:8080/cs/blogs/wego/archive/2007/09/19/oracle-article.aspx

[@more@]

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

转载于:http://blog.itpub.net/67798/viewspace-1040715/