How to get a single value without using group by Oracle

Question

I have this data and i need to combine all lines in a row in field fullname and get a single value from 3 equals from order field. How can i do that without using a group by?

Existing data

id order fullname
1 32 Jack Stinky Potato
2 32 Kevin Enormous Cucumber
3 32 Jerald Sad Onion

Expecting result

32 Jack Stinky Potato, Kevin Enormous Cucumber, Jerald Sad Onion

using group by would write

select order, wm_concat(fullname) from EmployeeCards
group by order

or this, but it doesn't rational.

 select wm_concat(unique order), wm_concat(fullname) from EmployeeCards

or just select (unique order), wm_concat(fullname) from EmployeeCards don't working. Which aggregate function shoul i use to get a single value? Thanks

Answer 1

Use LISTAGG:

SELECT
    "order",
    LISTAGG(fullname, ',') WITHIN GROUP (ORDER BY id) AS fullnames
FROM EmployeeCards
GROUP BY
    "order";

Demo

Also, please avoid naming your database objects (e.g. tables, columns, etc.) using reserved SQL keywords, such as ORDER.