Oracle Column Values Merge

The most common way to merge column values is to write a custom function to do this, and here's another way.

You can use the for XML Path in SQL Server to merge column values, and you can use Wm_concat or Listagg in Oracle.

Prepare data:

1 2 3 4 5 6 7 8 9 10 11-12

CREATE TABLE MyTest (xType number,city nvarchar2 (200)); /INSERT into MyTest (xtype,city) Select 1,n ' Beijing ' from dual UNION all select 1,n ' Shanghai ' from dual union ALL select 1,n ' Guangzhou ' FR OM Dual UNION ALL select 2,n ' Wuhan ' from dual union ALL select 2,n ' Hangzhou ' from dual union ALL select 2,n ' Xiamen ' from dual COMMIT; /

Use Wm_concat:

1 2 3

SELECT Xtype,wmsys.wm_concat (To_char) as xcity from MyTest GROUP by XType

Use Listagg:

1 2 3 4

SELECT XType, Listagg (To_char (city), ', ') WITHIN GROUP (order by XType) as xcity from MyTest GROUP by XType

Outcome:

XTYPE xcity
---------- --------------------------------------
1 Beijing, Guangzhou, Shanghai
2 Hangzhou, Wuhan, Xiamen

To be registered:

0, the above in the city column before all added To_char () function, is to prevent the situation of the Code;

1, Wm_concat in the official documents of Oracel, can not guarantee the compatibility of each version;

2, Listagg is the 11g version of the new aggregation function.