CODEWITHSUNDEEP
sqloracle-databasemergeunion
ifuwannaride
ifuwannaride

Reputation: 121

Union merging different columns

I have two tables:

Table1:

DATAID|   NAME   | FACTOR

 1    |   Ann    |   1
 2    |   Kate   |   1
 3    |   Piter  |   1

Table2:

DATAID|   NAME   | FACTOR

 1    |   John    |   2
 6    |   Arse    |   2
 3    |   Garry   |   2

I would like UNION those tables and get this result:

DATAID|   NAME    | FACTOR

 1    |   Ann     |   1,2
 2    |   Kate    |   1
 3    |   Piter   |   1,2
 6    |   Arse    |   2

So when there's 2 rows with same dataid, I would like to get 'NAME' column from Table1 and some kind of aggregated FACTOR, for example '1,2' or 3

Upvotes: 0

Views: 72

Answers (2)

user5683823
user5683823

Reputation:

Something like this should work. In your actual situation you will not need the first two CTE's (the subqueries in the WITH clause I added for testing).

with 
     table1 ( dataid, name, factor ) as (
       select 1, 'Ann'  , 1 from dual union all
       select 2, 'Kate' , 1 from dual union all
       select 3, 'Piter', 1 from dual
     ),
     table2 ( dataid, name, factor ) as (
       select 1, 'John' , 2 from dual union all
       select 6, 'Arse' , 2 from dual union all
       select 3, 'Garry', 2 from dual
     ),
     u ( dataid, name, factor, source ) as (
       select dataid, name, factor, 1 from table1
       union all
       select dataid, name, factor, 2 from table2
     ),
     z ( dataid, name, factor ) as (
       select dataid, first_value(name) over (partition by dataid order by source), 
              factor
       from   u
     )
select dataid, name,
       listagg(factor, ',') within group (order by factor) as factor
from   z
group by dataid, name
order by dataid
;

Output:

 DATAID NAME  FACTOR
------- ----- ---------
      1 Ann   1,2
      2 Kate  1
      3 Piter 1,2
      6 Arse  2

4 rows selected.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One method uses listagg():

select dataid, name,
       listagg(factor, ',') within group (order by factor) as factors
from ((select dataid, name, factor from table1 t1
      ) union all
      (select dataid, name, factor from table2 t2
      )
     ) t
group by dataid, name;

Note: I notice that the names are not the same for a given id. You can choose one by using aggregation functions.

Or, if you only have one row in each table, you can use a full outer join:

select coalesce(t1.dataid, t2.dataid) as dataid,
       coalesce(t1.name, t2.name) as name,
       trim(leading ',' from coalesce(',' || t1.factor, ',') || coalesce(',' || t2.factor, '') as factors
from t1 full outer join
     t2
     on t1.dataid = t2.dataid;

Upvotes: 1

Related Questions