7 Replies Latest reply: Dec 2, 2010 4:29 PM by Aketi Jyuuzou RSS

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

    GVR
      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
        • 1. Re: How to remove gaps/null values from set of columns in a row
          Frank Kulash
          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'.
          • 2. Re: How to remove gaps/null values from set of columns in a row
            GVR
            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
            • 3. Re: How to remove gaps/null values from set of columns in a row
              Frank Kulash
              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.
              • 4. Re: How to remove gaps/null values from set of columns in a row
                odie_63
                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.
                • 5. Re: How to remove gaps/null values from set of columns in a row
                  MichaelS
                  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.
                  • 6. Re: How to remove gaps/null values from set of columns in a row
                    Aketi Jyuuzou
                    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
                    • 7. Re: How to remove gaps/null values from set of columns in a row
                      Aketi Jyuuzou
                      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