2015.12.24 (Christmas) A simple way to resolve a Oralce database to merge multiple rows with the same properties into one line for years to Wmsys.wm_concat

The function added after oralce10g is used Wmsys.wm_concat

For example, there are two fields in this table, to be merged into two lines of available SQL statements by airport_id

Select airport_id, Wmsys.wm_concat (account) from Airport_modify GROUP by airport_id

The result is:

Very simple, the drawback is that the merge results are not sorted, the simplest way to solve the sorting problem is to add distinct before merging columns

In addition, if the merged column results are garbled, you need to add to_char (), such as Wmsys.wm_concat (To_char (account)) before merging the columns, which is said to be because the function returns VARCHAR2 or CLOB two results in different versions of Oracle

2016.8.15 supplement, about sort

Sorting by Wmsys.wm_concat (distinct account) can only be done by the account text and cannot be sorted by a complex logic. Here's how it's implemented

There are normal SQL as follows

Select Flt,srtnum,seg,tran_level,name

From Flight_tran_direction

where seg>0

ORDER BY Flt,seg,srtnum

The result is: List all the points of the height-layer mismatch in all the routes

To implement each point string grouped by flt,seg and sorted by srtsum, the writable SQL is

Select Flt,seg,tran_level,

Wmsys. Wm_concat (name) over (PARTITION by flt,seg ORDER by Srtnum) KEY

From Flight_tran_direction

where seg>0

ORDER BY Flt,seg

The result: similar to group by FLT,SEG, the grouping by FLT,SEG is implemented, and name is sorted by Srtnum logic. The only disadvantage is that a group will have several more historical rows. You actually only need the last row for each group.

Improved SQL: Nesting a layer of groupby structures outside, with Max implementing the most merged columns

Select Flt,seg,max (key) RT

From

(

Select Flt,seg,tran_level,

Wmsys. Wm_concat (name) over (PARTITION by flt,seg ORDER by Srtnum) KEY

From Flight_tran_direction

where seg>0

ORDER BY Flt,seg

)

GROUP BY Flt,seg,tran_level

The result is:

Even by merging column RT groups, combining FLT

Select Rt,tran, Wmsys. Wm_concat (distinct FLT)

From

(

Select Flt,seg,

Case MoD (To_number (Regexp_substr (tran_level, ' ^[[:d igit:]]+ ')), 2) When 1 Then ' Xidan ' Else ' East Double ' End Tran,

Replace (max (key), ', ', '-') RT

From

(

Select Flt,seg,tran_level,

Wmsys. Wm_concat (name) over (PARTITION by flt,seg ORDER by Srtnum) KEY

From Flight_tran_direction

where seg>0

ORDER BY Flt,seg

)

GROUP BY Flt,seg,tran_level

)

GROUP BY Rt,tran

ORDER BY Rt,tran

2015.12.24 (Christmas) A simple way to resolve a Oralce database to merge multiple rows with the same properties into one line for years to Wmsys.wm_concat