Cannot perform an aggregate function on an expression containing an aggregate or a subquery with STRING_AGG

Question

I am trying to query information from 2 tables. The PrimaryTable I simply want to return all columns from all rows in that table. The SupportTable I want to just get all of the ID's from that table that are associated with a row in the PrimaryTable and return that as a comma separated string. Note: the SupportTable might have zero or many rows that are associated with a row in the PrimaryTable.

Here's the query I tried that is throwing the error.

SELECT
    PrimaryTable.*,
    STRING_AGG(
        (
            SELECT
                SupportTable.Id
            FROM
                SupportTable
            WHERE
                SupportTable.AssociatedPrimaryTableId = PrimaryTable.Id
        ),
    ',') AS AssociatedSupportTableIds
FROM
    PrimaryTable;

I've found other SO posts that address the error message I'm seeing, but none of those solutions use STRING_AGG() and they all seem to do GROUP BY in their primary table which I don't want to do.

Answer 1

they all seem to do GROUP BY in their primary table which I don't want to do.

Too bad. You will need GROUP BY for this, but maybe not the way you expect. Try building the column like this:

SELECT
    PrimaryTable.*,
    (
        SELECT
            STRING_AGG(SupportTable.Id, ',')
        FROM
            SupportTable
        WHERE
            SupportTable.AssociatedPrimaryTableId = PrimaryTable.Id
        GROUP BY AssociatedPrimaryTableId 
    )
    AS AssociatedSupportTableIds
FROM
    PrimaryTable;