CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Tamil Mani
Tamil Mani

Reputation: 64

Finding nth not null value from table

I have table like below which have data like

| cust  |   val1  | val2  | val3  | val4  | val5  |
---------------------------------------------------
|  1    |    x1   |       |  x3   |       |       | 
|  2    |         |       |       | x4    |       | 
|  3    |    x1   |       |       |       |       |     
|  4    |    x1   |  x2   |  x3   | x4    |  x5   |

and i want output as

cust val1 val2  val3 val4  val5
1    x1    x3   
2    x4 
3    x1 
4    x1    x2   x3    x4    x5

create table test_nth_nonull (cust varchar2(3),val1 varchar2(3),val2 varchar2(3),val3 varchar2(3), val4 varchar2(3), val5 varchar2(3));
insert into  test_nth_nonull values(1,'x1',null,'x3',null,null);
insert into  test_nth_nonull values(2,null,null,null,'x4',null);
insert into  test_nth_nonull values(3,'x1',null,null,null,null);
insert into  test_nth_nonull values(4,'x1','x2','x3','x4','x5');

select* from test_nth_nonull;

please help to achieve this in query

Upvotes: 0

Views: 268

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would unpivot and pivot the data using a lateral join:

select t.*, x.*
from test_nth_nonull t cross join lateral
     (select max(case when seqnum = 1 then val end) as val_1,
             max(case when seqnum = 2 then val end) as val_2,
             max(case when seqnum = 3 then val end) as val_3,
             max(case when seqnum = 4 then val end) as val_4,
             max(case when seqnum = 5 then val end) as val_5
      from (select val, row_number() over (order by n) as seqnum
            from (select 1 as n, t.val1 as val from dual union all
                  select 2, t.val2 as val from dual union all
                  select 3, t.val3 as val from dual union all
                  select 4, t.val4 as val from dual union all
                  select 5, t.val5 as val from dual
                 ) x
            where val is not null
           ) x
     ) x;

The advantage of this version over putting the values in a string and processing the string is that this preserves the original data types of the column.

The advantage of doing this over a pivot/unpivot over the entire table should be performance. The pivoting is only within each row, which should be faster.

Upvotes: 0

Tomáš Záluský
Tomáš Záluský

Reputation: 12010

You can utilize pivot and unpivot clause. First unpivot columns to rows to get row numbers (the clause ignores nulls by default), then pivot "shook-down" values back to rows to their new positions:

with test_nth_nonull (cust,val1,val2,val3,val4,val5) as (
  select 1,'x1',null,'x3',null,null from dual union all
  select 2,null,null,null,'x4',null from dual union all
  select 3,'x1',null,null,null,null from dual union all
  select 4,'x1','x2','x3','x4','x5' from dual
), unpivotted as (
  select *
  from test_nth_nonull
  unpivot (val for col in (val1 as 1, val2 as 2, val3 as 3, val4 as 4, val5 as 5))
), numbered as (
  select u.cust, row_number() over (partition by u.cust order by u.col) as rn, u.val
  from unpivotted u
)
select *
from numbered
pivot (max(val) for rn in (1 as val1, 2 as val2, 3 as val3, 4 as val4, 5 as val5))

Db fiddle here.

Upvotes: 2

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Using nvl() and decode() along with cte is gonna give you what you want. nvl(val1,val2) will return val2 if val1 is null other wise it will return val1. decode(val2,val1,null,val2) means if val2 =val1 then null else val2

with cte as (
select cust , nvl(nvl(nvl(nvl(val1 ,val2) ,val3 ), val4 ), val5 )val1,nvl(nvl(nvl(val2 ,val3 ), val4 ), val5 )val2,nvl(nvl(val3, val4 ), val5 )val3,nvl( val4 , val5 )val4,val5
 from test_nth_nonull
 )
 select cust,val1,decode(val2,val1,null,val2)val2,decode(val3,val2,null,val1,null,val3)val3,decode(val4,val3,null,val2,null,val1,null,val4)val4,decode(val5,val4,null,val3,null,val2,null,val1,null,val5) val5 from cte

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

Here's one option.

This is what you have:

SQL> select* from test_nth_nonull order by cust;

CUST   VAL1  VAL2  VAL3  VAL4  VAL5
------ ----- ----- ----- ----- -----
1      x1          x3
2                        x4
3      x1
4      x1    x2    x3    x4    x5

This is what you want (read comments within code):

SQL> with
  2  temp as
  3    -- concatenate all values
  4    (select cust, val1 ||';'|| val2||';'|| val3||';'|| val4||';'|| val5 val
  5     from test_nth_nonull
  6    ),
  7  temp2 as
  8    -- split concatenated column to rows
  9    (select cust,
 10       regexp_substr(val, '[^;]+', 1, column_value) val,
 11       column_value cv
 12     from temp
 13       cross join table(cast(multiset(select level from dual
 14                                      connect by level <= 5
 15                                     ) as sys.odcinumberlist))
 16    )
 17  -- final result
 18  select cust,
 19    max(case when cv = 1 then val end) v1,
 20    max(case when cv = 2 then val end) v2,
 21    max(case when cv = 3 then val end) v3,
 22    max(case when cv = 4 then val end) v4,
 23    max(case when cv = 5 then val end) v5
 24  from temp2
 25  group by cust
 26  order by cust;

CUST   V1   V2   V3   V4   V5
------ ---- ---- ---- ---- ----
1      x1   x3
2      x4
3      x1
4      x1   x2   x3   x4   x5

SQL>

Upvotes: 1

Related Questions