Oracle 10g: parsing 2 columns merging duplicates

问题:

I'm having a table with 3 columns: DATE_A, DATE_B and ISSUE DATE_A and DATE_B can be filled in 3 possible ways: either both have a value, or only one have, as shown here:

DATE_A    |  DATE_B   | ISSUE
----------+-----------+-----------
20130301  | 20140101  | bla 
20150801  | null      | foo
null      | 20180701  | bar

I need to parse this table to populate a new table, with DATE_A and DATE_B populating both a single column DATE_M. If the DATE_A (or DATE_B) value to insert into DATE_M already exists in DATE_M, then the source ISSUE must be appended with the existing DATE_M ISSUE. The example below show the principle.

Example

Source

DATE_A    |  DATE_B   | ISSUE
----------+-----------+-----------
20130301  | 20140101  | bla1
20150801  | null      | foo1
null      | 20180701  | bar
20130301  | 20150101  | bla2
20150801  | null      | foo2

Destination

DATE_M    | ISSUE
----------+-----------
20130301  | bla1; bla2
20140101  | bla1
20150801  | foo1; foo2
20150101  | bla2
20180701  | bar

Question

Is it possible to write a query doing that or should a stored procedure be written ? If a single query can, what could it be ?

回答1:

If I understand correctly, you want a union all of the date values and string aggregation. listagg() was introduced in 11g, but you can use wm_concat():

select dte, wm_concat(issue) as issues
from ((select date_a as dte, issue from t where date_a is not null) union all
      (select date_b, issue from t where date_b is not null)
     ) di
group by dte
order by dte;