竖列转成横列显示

最新推荐文章于 2024-03-19 18:36:23 发布

转载最新推荐文章于 2024-03-19 18:36:23 发布 · 483 阅读

文章标签：

#matlab

本文介绍了一种使用 Oracle SQL 的复杂查询方法来获取特定格式的数据集合。通过使用 `sys_connect_by_path` 函数配合 `row_number()` 和 `dense_rank()` 等窗口函数，实现了按特定顺序连接同一组内的记录，并在输出时对列之间加入逗号进行分隔。

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