Merge one column data in Oracle based on another two columns-CodePudding

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!

CodePudding user response：

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