CODEWITHSUNDEEP
sqloracle-database
Priyanka
Priyanka

Reputation: 307

Merge Columns in Oracle with distinct values

Need help to merge columns in Oracle with distinct values.

I've one table called TEST with below data.

ID  ID1   ID2   ID3
1   A     B     C
1   B     P     A
2   X     Y     Z
2   Y     Z     K

Need output as follows

ID  MergedValues
1   A;B;C;P
2   X;Y;Z;K

Upvotes: 1

Views: 1493

Answers (3)

Stew Ashton
Stew Ashton

Reputation: 1529

Here's my approach: (Note: after posting, I see this resembles Alex Poole's approach, except that I order the input rows first.)

  1. Order the input rows within each ID: you don't say how, I order by ID1,ID2,ID3
  2. Unpivot the data, assigning numbers from 1 to 3 to the columns
  3. Assign priorities to each value based on row order then column order
  4. When a value appears more than once, keep only the minimum "priority"
  5. Use LISTAGG, ordering by priority.
    with data_with_rn as (
      select t.*,
      row_number() over(partition by id order by ID1,ID2,ID3) rn
      from t
    )
    , unpivoted as (
      select id, val,
      row_number() over(partition by id order by rn, col) priority
      from data_with_rn
      unpivot(val for col in(ID1 as 1, ID2 as 2, ID3 as 3))
    )
    , grouped as (
      select id, val, min(priority) priority
      from unpivoted
      group by id, val
    )
    select id, listagg(val, ';') within group(order by priority) vals 
    from grouped
    group by id
    order by id;

ID  VALS
--  --------
1   A;B;C;P
2   X;Y;Z;K

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220762

This solution is close:

SELECT id, listagg(v, ';') WITHIN GROUP (ORDER BY v) AS MergedValues
FROM (
  SELECT id, id1 AS v
  FROM test
  UNION 
  SELECT id, id2 AS v
  FROM test
  UNION
  SELECT id, id3 AS v
  FROM test
) t
GROUP BY id

SQLFiddle

It does not retain the order of encounter of MergedValues as you seem to have requested implicitly, but produces this:

| ID | MERGEDVALUES |
|----|--------------|
|  1 |      A;B;C;P |
|  2 |      K;X;Y;Z |

Upvotes: 4

Alex Poole
Alex Poole

Reputation: 191235

You can unpivot the columns into rows, and find the distinct values to remove duplicates:

select distinct id, val
from test
unpivot (val for pos in (id1 as 1, id2 as 2, id3 as 3));

And then apply listagg() to that:

select id,
  listagg(val, ';') within group (order by val) as mergedvalues
from (
  select distinct id, val
  from test
  unpivot (val for pos in (id1 as 1, id2 as 2, id3 as 3))
)
group by id
order by id;

With your sample data as a CTE:

with test (ID, ID1, ID2, ID3) as (
            select 1, 'A', 'B', 'C' from dual
  union all select 1, 'B', 'P', 'A' from dual
  union all select 2, 'X', 'Y', 'Z' from dual
  union all select 2, 'Y', 'Z', 'K' from dual
)
select id,
  listagg(val, ';') within group (order by val) as mergedvalues
from (
  select distinct id, val
  from test
  unpivot (val for pos in (id1 as 1, id2 as 2, id3 as 3))
)
group by id
order by id;

        ID MERGEDVALUES                  
---------- ------------------------------
         1 A;B;C;P                       
         2 K;X;Y;Z

If the order within the list needs to match what you showed then it seems almost to be based on the first column the value was seen in, so you can do:

select id,
  listagg(val, ';') within group (order by min_pos) as mergedvalues
from (
  select id, val, min(pos) as min_pos
  from test
  unpivot (val for pos in (id1 as 1, id2 as 2, id3 as 3))
  group by id, val
)
group by id
order by id;

        ID MERGEDVALUES                  
---------- ------------------------------
         1 A;B;P;C                       
         2 X;Y;Z;K

which is closer but has C and P reversed; it isn't clear what should control that. Perhaps there is another column you haven't shown which implies a row order.

Upvotes: 4

Related Questions