WM_CONCAT

Check out an XMLAGG trick to do this:

https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html?&anchorAnswerId=25864822#a25864822

or you can write your own string aggregrate (also usable as an analytic)

you can find similar functions with other names,  stragg is the most common I've found, probably because it was posted on Tom Kyte's askTom page.

CREATE OR REPLACE TYPE string_agg_type
AS
    OBJECT(total varchar2(4000),
           STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
               RETURN number,
           MEMBER FUNCTION odciaggregateiterate(self IN OUT string_agg_type, VALUE IN varchar2)
               RETURN number,
           MEMBER FUNCTION odciaggregateterminate(self IN string_agg_type, returnvalue OUT varchar2,
           flags IN number)
               RETURN number,
           MEMBER FUNCTION odciaggregatemerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
               RETURN number);

CREATE OR REPLACE TYPE BODY string_agg_type
IS
    STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
        RETURN number
    IS
    BEGIN
        sctx   := string_agg_type(NULL);
        RETURN odciconst.success;
    END;

    MEMBER FUNCTION odciaggregateiterate(self IN OUT string_agg_type, VALUE IN varchar2)
        RETURN number
    IS
    BEGIN
        self.total   := self.total || ',' || VALUE;
        RETURN odciconst.success;
    END;

    MEMBER FUNCTION odciaggregateterminate(self IN string_agg_type, returnvalue OUT varchar2,
    flags IN number)
        RETURN number
    IS
    BEGIN
        returnvalue   := LTRIM(self.total, ',');
        RETURN odciconst.success;
    END;

    MEMBER FUNCTION odciaggregatemerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
        RETURN number
    IS
    BEGIN
        self.total   := self.total || ctx2.total;
        RETURN odciconst.success;
    END;
END;

CREATE OR REPLACE FUNCTION stragg(input varchar2)
    RETURN varchar2
    PARALLEL_ENABLE
    AGGREGATE USING string_agg_type;

Select allOpen in new window

why shouldn't you use wm_concat?

not supported - means if you call Oracle Support for help, you won't get it.

if you patch or upgrade, your code may break if the function changes or disappears

the function isn't documented,  there is no guarantee it will consistently perform as you expect it to.

check this link
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php 

performance wise for generic solutions...

the COLLECT with tbl2str method is the fastest if you don't have LISTAGG
next best is usually stragg, then xml aggregation, and finally the connect by (which is awful)

I often recommend the xml aggregation method (as in the link slightwv posted) because it doesn't require creating any objects (types or functions)
and it's often adequate for performance


For a non-generic solution.  a dedicated procedure will probably be faster than the others, but possibly not enough to really notice the difference over
collect/tbl2str

of course, I recommend testing for yourself to confirm any claims.