Multi-column switching in oracle

Method 1: through the wm_concat function, this function can be used in 10g, not compatible in 11g, removed from 12g, and the return type is varchar.

Syntax: wm_concat (column)

Example: Select wm_concat (Rownum) From dual Connect By Rownum < 10

Advantages: simple syntax

Disadvantages: character length can not exceed 4000, separated by commas, if you want to be separated by other symbols, but also replace, the performance is relatively poor

Method 2: through lisagg, the return type is varchar

Syntax: listagg (parameter, 'delimiter') within group (order by parameter id)

Example: Select

Listagg (Rownum,';') Within group (Order By Rownum Desc)

From dual Connect By Rownum < 10

Advantages: it can be sorted, and the delimiters can be customized, and it is efficient.

Disadvantages: the length of stitching characters cannot exceed 4000

Method 3: through xmlagg, it is used to parse MXL, or it can be used as character concatenation to return the clob type.

Syntax:

XMLAGG (XMLPARSE (CONTENT field | | WELLFORMED) ORDER BY field) .GETCLOBVAL ()

Or

XMLAGG (XMLELEMENT (e, string, delimiter) .Extract ('/ / text ()') .GETCLOBVAL ()

Example:

You can use either of the following:

Select Xmlagg (Xmlparse (Content Rownum | |', 'Wellformed) Order By Rownum Desc)

.Getclobval ()

From Dual

Connect By Rownum < 30000

Select Xmlagg (Xmlelement (e, Rownum,','). Extract ('/ / text ()') .Getclobval ()

From Dual

Connect By Rownum < 30

Advantages: there is no length limit for character stitching

Disadvantages: syntax is complex.