Oracle SQL query CONCAT, GROUP BY-CodePudding

I have this table

I wanted to sort by POS ASC and also aggregate the two columns X,Y so that my ID becomes Unique. So the result should be like this.

I tried for many hours and I could aggregate the column X,Y. But when I'm doing a GROUP BY ID and then WMCONCAT(X,Y) I couldn't sort it by the POS col....

Any help maybe..? Thanks.

CodePudding user response：

Instead of using WM_CONCAT, try using the LISTAGG aggregate function: it allows you to specify an ordering of your choice:

SELECT ID,  
       LISTAGG('(' || X || ', ' || Y || ')', ', ') WITHIN GROUP (ORDER BY POS) AS XY
FROM tab
GROUP BY ID

Check the demo here.