Aggragate Multiple Columns Using LISTAGG()-CodePudding

How can I concatenate the results of a column from multiple rows into a single row? My query is:

SELECT id AS "Record ID", ins_type AS "Inspection Type", stat AS "Status"
FROM records
ORDER BY id;

Result:

Where the id is the same, I would like to aggregate any multiple inspection types into a single column, like this:

Record ID       Inspection Type                                         Status
B202105148      Final Building, Final Electrical, Framing & Flashing    Approved
B202105143      Roof Framing/Sheathing                                  Approved

I am using Oracle Database version 12.1.0.2.0.

I tried using the LISTAGG() function:

SELECT id AS "Record ID", LISTAGG( ins_type, ', ' ) WITHIN GROUP ( ORDER BY ins_type ) AS "Inspection Type", stat AS "Status"
FROM records
ORDER BY id;

But I got:

ORA-00937: not a single-group group function.

CodePudding user response：

you missed group by

SELECT id AS "Record ID", 
LISTAGG( ins_type, ', ' ) WITHIN GROUP ( ORDER BY ins_type ) AS "Inspection Type", stat AS "Status"
FROM records
group by id,stat
ORDER BY id;

CodePudding user response：

From comments:

What's the expected result if Framing & Flashing isn't Approved?

Null would be expected.

You need to either use an aggregation function on the stat column or add it to the GROUP BY clause. Since you appear to want to only display Approved if all the records are Approved then you can use:

SELECT id AS "Record ID", 
       LISTAGG( ins_type, ', ' ) WITHIN GROUP ( ORDER BY ins_type ) AS "Inspection Type",
       CASE
       WHEN COUNT(CASE WHEN stat = 'Approved' THEN 1 END) = COUNT(*)
       THEN 'Approved'
       END AS "Status"
FROM   records
GROUP BY id
ORDER BY id;