How can I combine multiple rows into a comma-delimited list in Oracle?

Scrapping repeated info

Banish repeated values using DISTINCT keyword within LISTAGG.

SELECT LISTAGG(DISTINCT column, ',') WITHIN GROUP (ORDER BY column) AS unique_list FROM table;


Now, you have lists cleaner than my code on a Friday!

Dealing with older Oracle versions

Oracle version older than your grandpa (pre-11g Release 2)? No worries, here's what you can do:

• WM_CONCAT: It's deprecated but does the job like a loyal intern working post hours with no Starbucks in hand.
• SYS_CONNECT_BY_PATH: Remember, this requires a bit more setup (a hierarchical query) and a “trimming” process to get rid of any excess commas. A task as delicate as defusing a ticking bomb.

SELECT RTRIM(
    SYS_CONNECT_BY_PATH(column_name, ','), 
    ','
) AS combined_list
FROM (
    SELECT column_name,
    ROW_NUMBER() OVER (ORDER BY column_name) AS rn
    FROM table_name
)
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR rn = rn - 1
START WITH rn = 1; -- engage defusing the bomb!


Heavy-duty SQL checks

Tackling long lists

For lists longer than a student's excuse list, Oracle's LISTAGG has a character limit. Break the aggregation into partitions:

SELECT 
    LISTAGG(column, ',') WITHIN GROUP (ORDER BY column) 
        OVER (PARTITION BY partition_column) as part_lists
FROM table; -- Slice and dice like a hacking ninja.


Condition-based lists

To include only values that meet certain conditions, use the CASE statement within LISTAGG.

SELECT LISTAGG(CASE WHEN condition THEN column ELSE NULL END, ',') 
    WITHIN GROUP (ORDER BY column) AS conditional_list
FROM table; -- as selective as my dog choosing his biscuits.


The XML magic trick

XML, they said? Bingo!

When LISTAGG stretches its limits or isn't available, **XMLAGG**and XMLELEMENT come to your rescue:

SELECT RTRIM(
    XMLAGG(XMLELEMENT(e, column || ',')).EXTRACT('//text()'), 
    ','
) AS xml_combined_list
FROM table; -- To every comma out there, 'You SHALL not pass!'


Feelin' vintage with older versions

For treasuring backwards compatibility, especially in pre-11g versions, rely on SYS_CONNECT_BY_PATH or XML methods.

The Oracle performance dope

SQL functions > Custom PL/SQL

Prefer direct Oracle-specific SQL functions over custom PL/SQL to save yourself from unnecessary headaches (read: debugging) and time.

Love efficiency

When you're typing up those delicate queries, put on your top hat and act like a butler - minimize I/O operations and avoid any rampant table scans or join parties.

Indexing for the gold

Every bookworm knows the importance of Indices - the same implies to your SQL queries. Ensure proper indexing to speed up the ORDER BY operation in LISTAGG.