ORACLE column value combination, oracle Column

Source: Internet
Author: User

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.

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

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.

In oracle statements, how does one combine the values in a column into a single value, separated by commas?

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.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019


Learn more >

Alibaba Cloud Free Trial


Learn more >

Contact Us

