CODEWITHSUNDEEP
sqloracle-database
user13619579
user13619579

Reputation: 19

Switch column values to the left

I have a table like below:

      Column A           Column B            Column C
     ----------          ---------         -----------
         1                   1                 2
         4                   3                 4
         3                  null               2
         12                  12                12
         15                  7                 7 
         8                   9                 6
         null                2                 2
         null                null              3

I need to move each column value from right to the left until each column have distinct values except the null values. Output must be like this:

      Column A           Column B            Column C
     ----------          ---------         -----------
         1                   2                null
         4                   3                null
         3                   2                null
         12                  null             null
         15                  7                null 
         8                   9                6
         2                   null             null
         3                   null             null

How can I do this with the simplest way?

Thanks,

Upvotes: 0

Views: 243

Answers (4)

user5683823
user5683823

Reputation:

If the input (and output) is three columns, as in your example, then a bit of brute force can give you an efficient query:

select coalesce(a, b, c)                                 as a
     , case when b != a                       then b
            when c != coalesce(a, b)          then c end as b
     , case when a != b and b != c and a != c then c end as c
from   t
;

It takes just a moment's thought to understand why this is correct (or, alternatively, you can throw a lot of test cases at it and be satisfied that since it gives the correct answer in all cases, it must be correct even if you don't understand why it is).

This is not easy to generalize though; if you had, say, eight columns in the input (and in the output), you would do better with a solution like Ponder Stibbons proposed. Note, though, that the number of columns (whether it is three or eight or 250) has to be known in advance for a standard SQL query; otherwise you will need to write a dynamic query, which is generally not seen as a good practice.

EDIT:

Here is a solution that generalizes easily to any number of columns (same in the input and in the output); the number of columns, though, must be known in advance (as well as the names of the columns and their order).

This solution is similar to the one posted by Ponder Stibbons. There are two main differences. First, I use the lateral clause, which is available in Oracle 12.1 and higher; this allows computations to be done separately in each row (instead of mixing all values from all rows together after unpivot, only to group them back into the initial rows via pivot). Second, the code is a bit more complicated, to handle the case when all values in a row are null.

select l.a, l.b, l.c
from   t,
  lateral (
    select a, b, c
    from   ( select  1, row_number() over (order by nvl2(v, min(o), null)) o, v
             from    ( select t.a, t.b, t.c from dual )
             unpivot include nulls (v for o in (a as 1, b as 2, c as 3))
             group   by v 
          ) 
    pivot (max(v) for o in (1 as a, 2 as b, 3 as c))
  ) l
;

EDIT 2

In comments after this answer, the OP states that his real-life data has five columns, rather than three. The first solution is hard to extend to five columns, but the second solution is easy to extend. I show how on dbfiddle.

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You can use the regexp_substr as follows:

Table data:

SQL> select a,b,c from t;

   A    B    C
---- ---- ----
   1    1    2
   4    3    4
   3         2
  12   12   12
  15    7    7
   8    9    6
        2    2
             3

8 rows selected.

Your query:

SQL> select regexp_substr(vals,'[^,]+',1,1) as a,
  2         regexp_substr(vals,'[^,]+',1,2) as b,
  3         regexp_substr(vals,'[^,]+',1,3) as c
  4    from (select rtrim(case when a is not null then a || ',' end ||
  5     case when b = a or b is null then null else b || ',' end ||
  6     case when b = c or a = c or c is null then null else c end, ',') as vals
  7    from t
  8  );

A   B   C
--- --- ---
1   2
4   3
3   2
12
15  7
8   9   6
2
3

8 rows selected.

SQL>

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

This combination of unpivot, pivot and conditional ordering returns desired output:

with 
  nn as (
   select id, col, case rn when 1 then val end val
   from (
     select id, col, val, row_number() over (partition by id, val order by col) rn
     from (select rownum id, a, b, c from t)
     unpivot (val for col in (a, b, c) ) ) ),
   mov as (
     select id, val, 
            row_number() over (partition by id 
                               order by case when val is not null then col end ) rn 
     from nn )
select * from mov pivot (max(val) for rn in (1 a, 2 b, 3 c))

dbfiddle

Subquery nn removes duplicated values, subquery mov based on conditional ordering moves them up. Then pivot transposes rows into columns, because they were unpivoted in first step.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Hmmm . . . one method would be to construct a string and then break it apart. For example:

select regexp_substr(str, '[^|]+', 1, 1) as a,
       regexp_substr(str, '[^|]+', 1, 2) as b,
       regexp_substr(str, '[^|]+', 1, 3) as c
from (select t.*,
             trim(leading '|' from
                  (case when a is not null then '|' || a end) ||
                  (case when b is not null then '|' || b end) ||
                  (case when c is not null then '|' || c end)
                 ) str
      from t
     ) t

Here is a db<>fiddle.

Upvotes: 0

Related Questions