多列转行命令

最新推荐文章于 2024-12-11 18:01:23 发布

原创最新推荐文章于 2024-12-11 18:01:23 发布 · 440 阅读

0 ·

CC 4.0 BY-SA版权

ORACLE技术笔记专栏收录该内容

30 篇文章

订阅专栏

本文介绍了一种使用Oracle SQL中的sys_connect_by_path和LISTAGG函数将分散的SQL语句片段连接成完整SQL语句的方法。这种方法适用于从stats$sqltext表中提取SQL语句并进行整合的情况。

将stats$sqltext 里的SQL语句连成一行出力

★sys_connect_by_path

select hv,'"'|| replace(st,'^','') || '"'
from(
select old_hash_value hv,rn r, sys_connect_by_path(sql_text,'^') st
from (
select old_hash_value,sql_text,
row_number() over(partition by old_hash_value order by piece) as rn
from stats$sqltext
where piece < 60)
where connect_by_isleaf = 1
start with rn = 1
connect by prior old_hash_value = old_hash_value
and prior rn = rn -1)

★LISTAGG
select old_hash_value,listagg(sql_text,'') within group(order by piece)
from(
select * from stats$sqltext
--where old_hash_value = 785023737
where piece < 50
)
group by old_hash_value