In Oracle, how can I concatenate multiple results returned from select statement into a comma-separated string?

you can create a function like stragg (search EE for "stragg")

or you can use the COLLECT built in function and then turn it into a string with tbl2str  (search EE for tbl2str)

or you can use xml built in aggregation, here's an example

https://www.experts-exchange.com/questions/24609896/Oracle-SQL-comma-string-comma-needed.html

note, the COLLECT way is the most efficient but does require creation of extra objects

select a || ', ' || b || ', ' || c
from table
where foo = bar

kaijaibe,  that will only concatenate multiple columns of a single row.

not single column of multiple rows

here's an example of stragg and xmlaggregation

https://www.experts-exchange.com/questions/24120361/Simple-Query.html

also note, when you upgrade to 11gR2 the LISTAGG function is built in

The hierarchical query method suggested by slightwv is extremely resource intensive compared to the other methods (of course, I always recommend testing yourself to verify)

The row_number/connect by method is, however, the only method available in version 9i that doesn't require the creation of additional types and/or functions.  

There is also a built-in function wm_concat in some versions (I think 10gr2+).
SQL> select ename from emp;

ENAME
---------
tony
Janice
marvin
zeek

SQL> select wm_concat(ename) as employees
  2  from emp;

EMPLOYEES
------------------------------------------
tony,Janice,marvin,zeek

wm_concat is not documented or supported by oracle.

I think wm_concat is an early attempt at what eventually became listagg in 11gR2

I am leaning to a solution by slightwv which he calls "neat trick" - from this link:
https://www.experts-exchange.com/questions/24912161/Relationship-Query-in-SQL.html
(the code from that article is shown below).
However, while it worked fine for the simplified table with just two columns, I have some incorrect result for a table with 3 comumns.
So it works file for the 2 columns returning this
Column1


----------------------------------

select stu_id, trim(',' from sys_connect_by_path(class_name, ',') )
from (
select stu_id, class_name,
row_number() over(partition by stu_id order by class_name) rn,
count(*) over(partition by stu_id) cp
from class_reg
)
where rn = cp
start with rn = 1
connect by prior stu_id = stu_id
and prior rn = rn - 1

your example only shows 2 columns,  what do you want with your 3 column table?

please give short example of inputs and expected output.

again, if you are on 10g or higher, I recommend testing the xml aggregation vs the hierarchical.
Not only is hierarchical more resource intensive it's also bigger code.

and, if you can create a type and a function, then I HIGHLY recommend using either the stragg or the collect method (stragg is simpler syntax in the long run, but collect is pretty easy too and more efficient)

Sorry, sent by accident.  I will repeat:
I am leaning to a solution by slightwv which he calls "neat trick" - from this link:
https://www.experts-exchange.com/questions/24912161/Relationship-Query-in-SQL.html
(the code from that article is shown below).

However, while it worked fine for the simplified table with just two columns, I have some incorrect result for a table with 3 comumns.

So it works file for the 2 columns returning this
Column1     Column2
1                 BB, CC, PP, DD

But I need to deal with 3 table column, such as
Column1      Column2     Column3
1                  11              BB, CC, PP
1                  12              PP, CD, HH
2                  11              AA, VV, BB
2                  12              KK, BB, NN

I tried but could not modernize the code below to work with two prior levels instead of just one level

Could you please suggest?

what  does the input look like that produces this output?

Column1      Column2     Column3
1                  11              BB, CC, PP
1                  12              PP, CD, HH
2                  11              AA, VV, BB
2                  12              KK, BB, NN

I didn't read all the replies, but here is a pipeline function (inside the /* and */ coment.  Below that is an example of how you can call it.    Below that is another example that puts quotes around each item.

/**************************************************************************
***************************************************************************
This query utilized the function 'join' which must exist and is listed below.
**************************************************************************/
/*
create or replace function join
(
    p_cursor sys_refcursor,
    p_del varchar2 := ','
) return varchar2
is
    l_value   varchar2(32767);
    l_result  varchar2(32767);
begin
    loop
        fetch p_cursor into l_value;
        exit when p_cursor%notfound;
        if l_result is not null then
            l_result := l_result || p_del;
        end if;
        l_result := l_result || l_value;
    end loop;
    return l_result;
end join;
*/

set linesize 2000
set sqlcase upper
set heading off
set verify off
set feedback off

select join(cursor(select decode(rownum,1,'('||COLUMN_NAME,COLUMN_NAME) columns
from user_tab_columns where table_name = '&table_name')) || ')' from dual;

select join(cursor(select decode(rownum,1,'('''||COLUMN_NAME,COLUMN_NAME)
from user_tab_columns where table_name = '&table_name'), ''',''') || ''')' column_list from dual

set sqlcase mixed
set heading on
set feedback on
set verify o

The input looks that this:
Column1      Column2     Column3
1                  11              BB
1                  11              CC
1                  11              PP
1                  12              PP
1                  12              CD
1                  12              HH
2                  11              AA
2                  11              VV
2                  11              BB
2                  12              KK
2                  12              BB
2                  12              NN

but... if you really want to use the hierarchical version...


SELECT column1, column2, MAX(SUBSTR(SYS_CONNECT_BY_PATH(column3, ','), 2)) x
FROM (SELECT column1,
             column2,
             column3,
             ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column3) curr,
             ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY column3) - 1 prev
      FROM yourtable)
START WITH curr = 1
CONNECT BY PRIOR curr = prev AND column1 = PRIOR column1 AND column2 = PRIOR column2
GROUP BY column1, column2
ORDER BY column1, column2;

Sorry sdstuder.  I concede the point.  XMLAGG is by far the cleanest/fastest.  I saw stragg and jumped to the Tom Kyte stragg series of functions.  I never followed the links to see the xmlagg.

if your data sets are small enough, you might not notice the difference, but the xml version is faster, uses fewer sort steps, less memory, less cpu.  So it won't scale well as the data volume grows.

But, I would look into the option of creating a type and function like in the snippet below
makes the query easier to write as well as more efficient


SELECT column1, column2, tbl2str(CAST(COLLECT(column3) AS vcarray))
FROM yourtable
GROUP BY column1, column2;

CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000);
 
CREATE OR REPLACE FUNCTION tbl2str(p_tbl IN vcarray, p_delimiter IN VARCHAR2 DEFAULT ',' )
    RETURN VARCHAR2
    DETERMINISTIC
IS
    v_str VARCHAR2(32767);
BEGIN
    IF p_tbl.COUNT > 0
    THEN
        v_str := p_tbl(1);
 
        FOR i IN 2 .. p_tbl.COUNT
        LOOP
            v_str := v_str || p_delimiter || p_tbl(i);
        END LOOP;
    END IF;
 
    RETURN v_str;
END;

Select allOpen in new window

slightwv,
I still like stragg over the other methods when performance isn't critical.
you just can't beat this for easy, and it's not too bad performance wise either. Not the best, but not the worst

SELECT column1, column2, stragg(column3)
FROM yourtable
GROUP BY column1, column2;

sdstuber and sliqhtwv,

Thank you for your answers.

I am accepting the XMLAGG solution suggested by sdstuber, but I will also accept the SYS_CONNECT_BY_PATH solution as assisted solution as I worked with both of them and they are very useful.

I'll try to split the points.
Thank you very much!!!

I also like stragg for its simplicity, but Oracle returned  ORA-00904: "STRAGG": invalid identifier

you have to create stragg and it's underlying types (the second link I posted above shows them)

there are a lot of implementations of stragg on the internet
but due to the constraints of the cartridge api, there is very little differentiation between, only minor naming conventions.  They should all work fine