I am trying to build a report in Oracle and I am struggling grouping the data by two columns (client and user) and concatenating the other one (operation).
Basically I have this data:
| Client | Operation | User |
|---|---|---|
| Client A | 1 | John Smith |
| Client A | 2 | John Smith |
| Client A | 1 | Peter Brown |
| Client B | 3 | Mike Kennedy |
and this is the expected result:
| Client | Operation | User |
|---|---|---|
| Client A | 1;2 | John Smith |
| Client A | 1 | Peter Brown |
| Client B | 3 | Mike Kennedy |
I am trying tro group the data by the columns client and user by I get the error "not a group by expression".
Can anyone help me with the query?
Thanks a lot!
>Solution :
I think you’re looking for the listagg function:
SELECT client,
LISTAGG(operation, ',') WITHIN GROUP (ORDER BY operation),
user
FROM mytable
GROUP BY client, user