ORACLE column value combination

ORACLE column value combination

The most common method for merging column values is to validate a custom function. This describes other methods.

In SQL Server, you can use the For Xml Path For the value of the merge column, and wm_concat or listparts in Oracle.

Metric data:

Create table MyTest (xType NUMBER, City nvarchar2 (200);/insert into MyTest (xType, City) SELECT 1, N 'beijing' FROM dual union allselect 1, N 'shanghai' FROM dual union allselect 1, N 'guangzhou 'FROM dual union allselect 2, N 'wuhan' FROM dual union allselect 2, N 'hangzhou' FROM dual union allselect 2, N 'xiamen 'FROM dualCOMMIT ;/

Use wm_Concat:

SELECT xType,wmsys.wm_concat(to_char(City)) AS xCityFROM MyTestGROUP BY xType

Use listparts:

SELECT xType,       ListAgg(to_char(City),',') WITHIN GROUP(ORDER BY xType) AS xCityFROM MyTestGROUP BY xType

Result:

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

Zookeeper:

0. The To_Char () function is added in front of the City column to prevent unexpected results;

1. wm_concat does not exist in the official website of Alibaba El. It cannot guarantee the compatibility of each version;

2. listparts is a new aggregate function generated only in the 11g version.