Oracle SQL query CONCAT, GROUP BY

Question

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.

Please can you provide the SQL you are trying, so any errors you are getting. — MintBerryCRUNCH, Sep 22 '22 at 14:27

score 3 · Accepted Answer · answered Sep 22 '22 at 14:38

3

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.

answered Sep 22 '22 at 14:38

lemon

14,875
6
18
38

It works. But not for my table... I got error ORA-04189, it seems like that the string is too long. Do you have any solution for this? Since VARCHAR 4000 is maximum size. @lemon – CodeoDE Sep 27 '22 at 06:56
Check here >> https://stackoverflow.com/questions/29776035/oracle-ora-01489-result-of-string-concatenation-is-too-long. – lemon Sep 27 '22 at 12:18

Oracle SQL query CONCAT, GROUP BY

1 Answers1