Got it, let's work through this problem together! You mentioned hitting an ORA error when adding more fields to your LISTAGG query, and you want to switch to a subquery-based approach in Oracle (since you’re familiar with using STUFF in SQL Server). First, let’s fix the syntax issues in your current query, then walk through valid solutions.

First: Fix Your Current Query’s Syntax #

Your existing query has a couple of issues—most notably, the nested SELECT (dd.my_id, dd.customer_name, ...) is invalid Oracle syntax, and there’s no aggregation logic to combine rows. Let’s fix that and build out working solutions.

Solution 1: Corrected LISTAGG (If You Want to Stick With It) #

If your original ORA error was ORA-01489 (result of string concatenation is too long), we can either adjust LISTAGG or use XMLAGG for longer strings. If it was a syntax error, here’s the proper LISTAGG implementation:

SELECT 
    d.id, 
    d.name, 
    d.date_sale, 
    d.address, 
    d.city, 
    d.state, 
    d.zipcode, 
    d.description, 
    d.explanation, 
    d.received_date,
    -- Combine table2 columns into a delimited string
    LISTAGG(
        dd.my_id || ', ' || dd.customer_name || ', ' || dd.category || ', ' || TO_CHAR(dd.transaction_date, 'YYYY-MM-DD'),
        ';'
    ) WITHIN GROUP (ORDER BY dd.transaction_date) AS grouped_columns
FROM table1 d 
LEFT JOIN table2 dd ON d.id = dd.my_id 
WHERE d.id = 1 AND d.isActive = 1
GROUP BY 
    d.id, 
    d.name, 
    d.date_sale, 
    d.address, 
    d.city, 
    d.state, 
    d.zipcode, 
    d.description, 
    d.explanation, 
    d.received_date;

For longer strings (to avoid ORA-01489), swap LISTAGG with XMLAGG:

SELECT 
    d.id, 
    d.name, 
    d.date_sale, 
    d.address, 
    d.city, 
    d.state, 
    d.zipcode, 
    d.description, 
    d.explanation, 
    d.received_date,
    RTRIM(
        XMLAGG(
            XMLELEMENT(E, dd.my_id || ', ' || dd.customer_name || ', ' || dd.category || ', ' || TO_CHAR(dd.transaction_date, 'YYYY-MM-DD') || ';')
            ORDER BY dd.transaction_date
        ).EXTRACT('//text()'),
        ';'
    ) AS grouped_columns
FROM table1 d 
LEFT JOIN table2 dd ON d.id = dd.my_id 
WHERE d.id = 1 AND d.isActive = 1
GROUP BY 
    d.id, 
    d.name, 
    d.date_sale, 
    d.address, 
    d.city, 
    d.state, 
    d.zipcode, 
    d.description, 
    d.explanation, 
    d.received_date;

Solution 2: Subquery-Based Approach (As You Requested) #

If you want to avoid LISTAGG entirely and use subqueries (similar to how you’d use STUFF in SQL Server), here are two solid options:

Option A: Correlated Subquery with SYS_CONNECT_BY_PATH #

This mimics the "string concatenation via hierarchy" pattern:

SELECT 
    d.id, 
    d.name, 
    d.date_sale, 
    d.address, 
    d.city, 
    d.state, 
    d.zipcode, 
    d.description, 
    d.explanation, 
    d.received_date,
    (
        SELECT RTRIM(
            SYS_CONNECT_BY_PATH(
                dd.my_id || ', ' || dd.customer_name || ', ' || dd.category || ', ' || TO_CHAR(dd.transaction_date, 'YYYY-MM-DD'),
                ';'
            ),
            ';'
        )
        FROM table2 dd
        WHERE dd.my_id = d.id
        START WITH dd.transaction_date = (SELECT MIN(transaction_date) FROM table2 WHERE my_id = d.id)
        CONNECT BY PRIOR ROWID = dd.ROWID - 1 -- Use ROWID or a unique key for ordering
        GROUP BY dd.my_id
    ) AS grouped_columns
FROM table1 d 
WHERE d.id = 1 AND d.isActive = 1;

Option B: Aggregation in a Subquery (Cleaner for Most Cases) #

Wrap the row-combining logic in a subquery, then join it back to your main table—this keeps your main query clean:

SELECT 
    d.id, 
    d.name, 
    d.date_sale, 
    d.address, 
    d.city, 
    d.state, 
    d.zipcode, 
    d.description, 
    d.explanation, 
    d.received_date,
    gc.grouped_columns
FROM table1 d 
LEFT JOIN (
    SELECT 
        my_id,
        LISTAGG(
            my_id || ', ' || customer_name || ', ' || category || ', ' || TO_CHAR(transaction_date, 'YYYY-MM-DD'),
            ';'
        ) WITHIN GROUP (ORDER BY transaction_date) AS grouped_columns
    FROM table2
    GROUP BY my_id
) gc ON d.id = gc.my_id
WHERE d.id = 1 AND d.isActive = 1;

Quick Notes for Context #

• SQL Server STUFF Equivalent: Oracle’s LISTAGG is the direct replacement for the common STUFF + FOR XML PATH pattern in SQL Server. The subquery approaches above give you alternatives if LISTAGG isn’t working for your use case.
• ORA Error Troubleshooting: If you’re still hitting errors, double-check:
  • All non-aggregated fields from table1 are in the GROUP BY clause (avoids ORA-00979).
  • Date fields are converted to strings with TO_CHAR before concatenation (avoids type mismatches).
  • Your concatenated string isn’t exceeding Oracle’s VARCHAR2 limit (use XMLAGG for longer strings).

内容的提问来源于stack exchange，提问作者user1250264