oracle - Two wm_concat in a single query -

consider table 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 concatenate field2 , field3 on basis of field1. desired result should -

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

the query using query given below -

select field1,   wm_concat(field2),   wm_concat(field3) temp_table group field1; 

here result getting -

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

please suggest me working query , if possible please explain behaviour.

i using oracle 10g , have tried using xmlagg.

you either use sys_connect_by_path or xmlagg alternatives wm_concat (unsupported). example sys_connect_by_path found in oracle forum discussion here. created sql fiddle using xmlagg here.

also, can use order clause (as shown in examples), if need values concatenated in order.

references:

listagg alternative in 10g

related question on so