ORACLE column value combination, oracle Column
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.
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
SELECT xType, ListAgg(to_char(City),',') WITHIN GROUP(ORDER BY xType) AS xCityFROM MyTestGROUP BY xTypeResult:
1 Beijing, Guangzhou, Shanghai
2 Hangzhou, Wuhan, Xiamen
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.
All versions of oracle can use select wm_concat (name) as name from user;
However, for oracle11g, select listparts (name, ',') within group (order by name) as name from user;
This method is more efficient and is recommended by the official team.
Oracle merges two columns
Simple select a | B is definitely wrong. You must have the same field to merge it.
So you have to add a row number to the subquery of two tables and associate them with two rows.