Forum Stats

  • 3,734,027 Users
  • 2,246,861 Discussions
  • 7,857,001 Comments

Discussions

How to remove gaps/null values from set of columns in a row

GVR
GVR Member Posts: 439
edited December 2010 in SQL & PL/SQL
Im trying to implement a solution for removing null value columns from a row.
Basically in below example i have five codes and corresponding id's for that codes.What im trying to achive here is if
i have a null code then i have to move next not null code and id into its new location.
Example:
'A1'cd1,'A2'cd2,null cd3,'A4'cd4,null cd5,'i1'id1,'i2'id2,null id3,'i4' id4,null id5 
So here cd4 and id4 should take positions of cd3 and id3.
Output should look like this
cd1 cd2 cd3 cd4 cd5     id1 id2 id3 id4 id5
A1  A2   A4              i1  i2  i4
Any help would be highly appreciated for below example:

with temp_table as
(select 'A1'cd1,'A2'cd2,null cd3,'A4'cd4,null cd5,'i1'id1,'i2'id2,null id3,'i4' id4,null id5 from dual union all
select 'A11',null,null,'A44','A55','id11',null,null, 'id44','id55' from dual union all
select null,'A111',null,null,'A555',null,'id111',null, null,'id555' from dual union all
select 'A',null,null,'A1111','E55','id11',null,null, 'id111','id1111' from dual )
select * from temp_table;
Edited by: GVR on Dec 1, 2010 8:27 AM

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    edited December 2010
    Hi,

    Here's one way to do it:
    (a) unpivot the data onto separate rows
    (b) use the analytic ROW_NUMBER function to number rows
    (c) pivot the data back to the original number of rows
    Exactly how to unpivot and pivot depends on your datbase version.

    What is the output you want from the 4 rows of sample data you posted?
    Make sure the column headings and data line up correctly after you post them. In the one-row output you posted,
    GVR wrote:
    ... Output should look like this
    cd1 cd2 cd3 cd4 cd5 id1 id2 id3 id4 id5
    A1  A2   A4              i1   i2   i4
    it looks like you want the id2 column to contain 'i1'.
  • GVR
    GVR Member Posts: 439
    Thanks Frank. I will try your options and sorry about the o/p format.

    o/p that im looking for is


    Output should look like this
    cd1	cd2	cd3	cd4	cd5	id1	id2	id3	id4	id5
    A1	A2	A4			i1	i2	i4			
    A11	A44	A55			id11	id44	id55			
    A111	A555				id111	id555		
    A	A1111	E55			id11	id111	id1111
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    edited December 2010
    Hi,

    It looks like you'd be better off storing the data in an un-pivoted form to begin with.
    The fact that soemthing entered in the cd5 column might appear in cd1, cd2, cd3 or cd4 suggests that they are all really the same entity, and they all belong in the same column. You could have another column to indicate if this was originally a cd1, cd2, ..., or cd5, if necessary. Not only would it make this job a little easier, it would allow you to have as many repetitions as you need (not just 5), and allow you to search for a given value regardless of wheteher it was a cd1, cd2, ... or cdn. If you have any validation on the column, you wouldn't need 5 nearly-identical constraints, or a trigger with 5 nearly-identical sections.
    Frank Kulash
  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    I second Frank's comment about the data model, but maybe you have no choice right now, so here's a possible way (using regular expressions, so 10g+) :
    WITH temp_table AS (
     select 'A1'cd1,'A2'cd2,null cd3,'A4'cd4,null cd5,'i1'id1,'i2'id2,null id3,'i4' id4,null id5 from dual union all
     select 'A11',null,null,'A44','A55','id11',null,null, 'id44','id55' from dual union all
     select null,'A111',null,null,'A555',null,'id111',null, null,'id555' from dual union all
     select 'A',null,null,'A1111','E55','id11',null,null, 'id111','id1111' from dual
    )
    SELECT regexp_substr(cd_list,'[^;]+',1,1) cd1,
           regexp_substr(cd_list,'[^;]+',1,2) cd2,
           regexp_substr(cd_list,'[^;]+',1,3) cd3,
           regexp_substr(cd_list,'[^;]+',1,4) cd4,
           regexp_substr(cd_list,'[^;]+',1,5) cd5,
           regexp_substr(id_list,'[^;]+',1,1) id1,
           regexp_substr(id_list,'[^;]+',1,2) id2,
           regexp_substr(id_list,'[^;]+',1,3) id3,
           regexp_substr(id_list,'[^;]+',1,4) id4,
           regexp_substr(id_list,'[^;]+',1,5) id5
    FROM (
     SELECT regexp_replace(cd1 ||';'|| cd2 ||';'|| cd3 ||';'|| cd4 ||';'|| cd5, '^;|(;){2,}', '\1') cd_list,
            regexp_replace(id1 ||';'|| id2 ||';'|| id3 ||';'|| id4 ||';'|| id5, '^;|(;){2,}', '\1') id_list
     FROM temp_table
    )
    ;
     
    CD1     CD2     CD3     CD4     CD5     ID1     ID2     ID3     ID4     ID5
    ------- ------- ------- ------- ------- ------- ------- ------- ------- -------
    A1      A2      A4                      i1      i2      i4
    A11     A44     A55                     id11    id44    id55
    A111    A555                            id111   id555
    A       A1111   E55                     id11    id111   id1111
     
    I chose the separator ';' as it doesn't appear in the data.
    odie_63
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Or
    SQL> WITH temp_table AS (
     select 'A1'cd1,'A2'cd2,null cd3,'A4'cd4,null cd5,'i1'id1,'i2'id2,null id3,'i4' id4,null id5 from dual union all
     select 'A11',null,null,'A44','A55','id11',null,null, 'id44','id55' from dual union all
     select null,'A111',null,null,'A555',null,'id111',null, null,'id555' from dual union all
     select 'A',null,null,'A1111','E55','id11',null,null, 'id111','id1111' from dual
    )
    --
    --
    select t.x1.extract ('*[1]/text()') cd1,
           t.x1.extract ('*[2]/text()') cd2,
           t.x1.extract ('*[3]/text()') cd3,
           t.x1.extract ('*[4]/text()') cd4,
           t.x1.extract ('*[5]/text()') cd5,
           t.x2.extract ('*[1]/text()') id1,
           t.x2.extract ('*[2]/text()') id2,
           t.x2.extract ('*[3]/text()') id3,
           t.x2.extract ('*[4]/text()') id4,
           t.x2.extract ('*[5]/text()') id5
      from (select xmlforest (cd1, cd2, cd3, cd4, cd5) x1, 
                   xmlforest (id1, id2, id3, id4, id5) x2 from temp_table
           ) t
    /
    CD1    CD2    CD3    CD4 CD5 ID1    ID2    ID3    ID4 ID5
    ------ ------ ------ --- --- ------ ------ ------ --- ---
    A1     A2     A4             i1     i2     i4            
    A11    A44    A55            id11   id44   id55          
    A111   A555                  id111  id555                
    A      A1111  E55            id11   id111  id1111        
    
    4 rows selected.
    MichaelS
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited December 2010
    I like case expression B-)
    The same question of my homepage http://www.geocities.jp/oraclesqlpuzzle/7-81.html
    with temp_table(cd1,cd2,cd3,cd4,cd5,id1,id2,id3,id4,id5) as(
    select 'A1' ,'A2' ,null,'A4'   ,null  ,'i1'  ,'i2'   ,null,'i4'   ,null     from dual union all
    select 'A11',null ,null,'A44'  ,'A55' ,'id11',null   ,null,'id44' ,'id55'   from dual union all
    select null,'A111',null,null   ,'A555',null  ,'id111',null,null   ,'id555'  from dual union all
    select 'A'  ,null ,null,'A1111','E55' ,'id11',null   ,null,'id111','id1111' from dual)
    select
    case when SumCD1 = 1 then CD1
         when SumCD1+SumCD2 = 1 then CD2
         when SumCD1+SumCD2+SumCD3 = 1 then CD3
         when SumCD1+SumCD2+SumCD3+SumCD4 = 1 then CD4
         when SumCD1+SumCD2+SumCD3+SumCD4+SumCD5 = 1 then CD5 end as CD1,
    case when SumCD1+SumCD2 = 2 then CD2
         when SumCD1+SumCD2+SumCD3 = 2 then CD3
         when SumCD1+SumCD2+SumCD3+SumCD4 = 2 then CD4
         when SumCD1+SumCD2+SumCD3+SumCD4+SumCD5 = 2 then CD5 end as CD2,
    case when SumCD1+SumCD2+SumCD3 = 3 then CD3
         when SumCD1+SumCD2+SumCD3+SumCD4 = 3 then CD4
         when SumCD1+SumCD2+SumCD3+SumCD4+SumCD5 = 3 then CD5 end as CD3,
    case when SumCD1+SumCD2+SumCD3+SumCD4 = 4 then CD4
         when SumCD1+SumCD2+SumCD3+SumCD4+SumCD5 = 4 then CD5 end as CD4,
    case when SumCD1+SumCD2+SumCD3+SumCD4+SumCD5 = 5 then CD5 end as CD5,
    case when SumID1 = 1 then ID1
         when SumID1+SumID2 = 1 then ID2
         when SumID1+SumID2+SumID3 = 1 then ID3
         when SumID1+SumID2+SumID3+SumID4 = 1 then ID4
         when SumID1+SumID2+SumID3+SumID4+SumID5 = 1 then ID5 end as ID1,
    case when SumID1+SumID2 = 2 then ID2
         when SumID1+SumID2+SumID3 = 2 then ID3
         when SumID1+SumID2+SumID3+SumID4 = 2 then ID4
         when SumID1+SumID2+SumID3+SumID4+SumID5 = 2 then ID5 end as ID2,
    case when SumID1+SumID2+SumID3 = 3 then ID3
         when SumID1+SumID2+SumID3+SumID4 = 3 then ID4
         when SumID1+SumID2+SumID3+SumID4+SumID5 = 3 then ID5 end as ID3,
    case when SumID1+SumID2+SumID3+SumID4 = 4 then ID4
         when SumID1+SumID2+SumID3+SumID4+SumID5 = 4 then ID5 end as ID4,
    case when SumID1+SumID2+SumID3+SumID4+SumID5 = 5 then ID5 end as ID5
    from (select cd1,cd2,cd3,cd4,cd5,id1,id2,id3,id4,id5,
          nvl2(cd1,1,0) as SumCD1,
          nvl2(cd2,1,0) as SumCD2,
          nvl2(cd3,1,0) as SumCD3,
          nvl2(cd4,1,0) as SumCD4,
          nvl2(cd5,1,0) as SumCD5,
          nvl2(id1,1,0) as SumID1,
          nvl2(id2,1,0) as SumID2,
          nvl2(id3,1,0) as SumID3,
          nvl2(id4,1,0) as SumID4,
          nvl2(id5,1,0) as SumID5
          from temp_table)
    order by cd1,cd2,cd3,cd4,cd5;
    
    CD1   CD2    CD3   CD4   CD5   ID1    ID2    ID3     ID4   ID5 
    ----  -----  ----  ----  ----  -----  -----  ------  ----  ----
    A     A1111  E55   null  null  id11   id111  id1111  null  null
    A1    A2     A4    null  null  i1     i2     i4      null  null
    A11   A44    A55   null  null  id11   id44   id55    null  null
    A111  A555   null  null  null  id111  id555  null    null  null
    My SQL articles of OTN-Japan
    http://www.oracle.com/technology/global/jp/pub/jp/ace/sql_image/1/otnj-sql-image1.html
    http://www.oracle.com/technology/global/jp/pub/jp/ace/sql_image/2/otnj-sql-image2.html
    Aketi Jyuuzou
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited December 2010
    Frank Kulash wrote:
    Hi,

    Here's one way to do it:
    (a) unpivot the data onto separate rows
    (b) use the analytic ROW_NUMBER function to number rows
    (c) pivot the data back to the original number of rows
    Exactly how to unpivot and pivot depends on your datbase version.
    Nice logic ;-)

    And there is another way to do it:
    (a) unpivot the data onto separate rows
    (b) use the analytic nth_value function with ignore nulls

    Hehehe I like nth_value B-)
    with temp_table(cd1,cd2,cd3,cd4,cd5,id1,id2,id3,id4,id5) as(
    select 'A1' ,'A2' ,null,'A4'   ,null  ,'i1'  ,'i2'   ,null,'i4'   ,null     from dual union all
    select 'A11',null ,null,'A44'  ,'A55' ,'id11',null   ,null,'id44' ,'id55'   from dual union all
    select null,'A111',null,null   ,'A555',null  ,'id111',null,null   ,'id555'  from dual union all
    select 'A'  ,null ,null,'A1111','E55' ,'id11',null   ,null,'id111','id1111' from dual)
    select distinct
    nth_value(CDs,1) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as cd1,
    nth_value(CDs,2) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as cd2,
    nth_value(CDs,3) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as cd3,
    nth_value(CDs,4) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as cd4,
    nth_value(CDs,5) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as cd5,
    nth_value(IDs,1) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as id1,
    nth_value(IDs,2) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as id2,
    nth_value(IDs,3) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as id3,
    nth_value(IDs,4) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as id4,
    nth_value(IDs,5) ignore nulls over(partition by rn
    order by SK Rows between Unbounded Preceding and Unbounded Following) as id5
    from (select cd1,cd2,cd3,cd4,cd5,
                 id1,id2,id3,id4,id5,RowNum as rn
          from temp_table)
    UnPivot((CDs,IDs) for SK in((cd1,id1) as 1,
                                (cd2,id2) as 2,
                                (cd3,id3) as 3,
                                (cd4,id4) as 4,
                                (cd5,id5) as 5))
    order by cd1,cd2,cd3,cd4,cd5;
    
    CD1    CD2    CD3    CD4   CD5    ID1    ID2    ID3     ID4   ID5 
    -----  -----  -----  ----  -----  -----  -----  ------  ----  ----
    A      A1111  E55    null  null   id11   id111  id1111  null  null
    A1     A2     A4     null  null   i1     i2     i4      null  null
    A11    A44    A55    null  null   id11   id44   id55    null  null
    A111   A555   null   null  null   id111  id555  null    null  null
    Aketi Jyuuzou
This discussion has been closed.