oracle竖行的两列变成横行_竖列转成横列显示

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