oracle pl/sql results into one string
11,799
Solution 1
Using SQL Fiddle:
select LISTAGG(name, ',') WITHIN GROUP (ORDER BY 1) AS names
from temp_table
Solution 2
Another option using pure SQL that will work before Oracle 11G, although is still limited to 4000 characters for the string.
Select ltrim(max(names), ', ') as names
From (
Select sys_connect_by_path(name, ' ,') as names
From (
Select name, row_number() over (order by name) as rown
From temp_table
)
Start with rown = 1
Connect by rown = prior rown + 1
)
Author by
sayhaha
Updated on June 09, 2022Comments
-
sayhaha almost 2 years
I'm trying to create a simple stored procedure that stores queried result into one string.
v_string1 varchar2(100); Select column1 From dual;
Will return
column 1 -------- aaaa bbbb cccc
I want to store "aaaa, bbbb, cccc' into v_string1. And all I can think of is a Cursor... Is there a better way to handle this?
-
Gerrat almost 12 yearsCoolio...I've never seen that function before +1!
-
Kyra almost 12 yearsThanks. I hadn't either until I googled it. Glad it helped and it caused me to learn something new too :D