多列转行命令

将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