Implementation of oracle column merge: oracle column merge

Source: Internet
Author: User

Implementation of oracle column merge: oracle column merge

In many cases, we will use oracle column merge. oracle provides the following methods to implement column merge:

1. Before Oracle 10 Gb, WMSYS. WM_CONCAT was used:

Wmsys. wm_concat separates the field values.

select id,wm_concat(name) from tab_name group by id;

2. Use sys_connect_by_path

Sys_connect_by_path (field name, The Connection Symbol between two fields). Do not use a comma for the Connection Symbol here. oracle will report an error. If necessary, replace it with replace, the method is as follows REPLACE (field name, original character ,','). Before using this function, you must create a tree. Otherwise, it is useless.

Iii. listparts can be used after Oracle 11G

select listagg(id,',') within group (order by id) col_name from tab_name;

4. Use custom functions:

create or replace function getRow(table1 varchar2 , ptdb1 varchar2) RETURN VARCHAR2 isResult VARCHAR2(1000);beginFOR cur IN (SELECT audit_code FROM sys_audit_column t2 WHERE table1 =t2.table_name and ptdb1 = t2.ptdb_name) LOOPRESULT := RESULT||cur.audit_code||',';END LOOP;RESULT:=rtrim(RESULT,',');return(Result);end getRow;

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.

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.