Two wm_concat in a single query

Question

Consider a table as follows -

TABLE DB.TEMP_TABLE
------------------------------------------  
field1        field2           field3
------------------------------------------  
1             10                100  
1             20                200  
1             30                300  
1             40                400
2             10                100  
2             20                200

I need to concatenate field2 and field3 on the basis of field1. The desired result should look like this -

DESIRED RESULT
------------------------------------------  
field1        field2           field3
------------------------------------------
1             10,20,30,40      100,200,300,400
2             10,20            100,200

The query I am using for this query is given below -

SELECT field1,
  wm_concat(field2),
  wm_concat(field3)
FROM temp_table
GROUP BY field1;

Here is the result I am getting -

------------------------------------------  
field1        field2           field3
------------------------------------------
1             10,20,40,30      100,400,300,200
2             10,20            100,200

Please suggest me a working query and if possible please explain this behaviour.

I am using Oracle 10g and have also tried using xmlagg.

score 1 · Answer 1 · answered Mar 10 '14 at 17:28

1

wm_concat is not documented/supported by Oracle, so there is no guarantee that it will be around in the future. You can use sub-queries for that. If you prefer using a function, you can create your own function and use it similar to wm_concat. Check this link.

answered Mar 10 '14 at 17:28

Racil Hilan

24,690
13
50
55

As I have already mentioned, I have also used `xmlagg`, but still getting the same result. – Gupta.Swap Mar 10 '14 at 17:32
OK, implement your own function. The link I mentioned does most of the work for you. – Racil Hilan Mar 10 '14 at 17:34
1

@RacilHilan `wm_concat` has indeed been removed from 12c. Which is a little surprising, I thought `wm_concat` was some sort of common-law feature by now. – Jon Heller Mar 10 '14 at 18:45
@jonearles Yes, you got it right, and like "common-law" you don't need a divorce to end it ;), and that's what 12c did. – Racil Hilan Mar 10 '14 at 18:53

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

You could either use SYS_CONNECT_BY_PATH or XMLAGG as alternatives to WM_CONCAT (unsupported). An example for SYS_CONNECT_BY_PATH is found in an Oracle Forum discussion here. I created a SQL Fiddle using XMLAGG here.

Also, you can use the ORDER BY clause (as shown in the examples), if you need the values concatenated in a certain order.

References:

LISTAGG alternative in 10g

Two wm_concat in a single query

2 Answers2