2 Replies Latest reply: Dec 29, 2009 1:17 PM by Hoek RSS

    Need to combine 2 row values into one

    Annas
      Hi
      The existing table values look like

      Column 1
      Ext-Stu
      Shortterm
      Internal student
      Foreign Student

      I need to insert the column 1 values as Ext-Stu shortterm to another table as a single row. The remaining 2 rows Internal Student and foreign student will be inserted as a separate values

      Thanks in advance

      regards
      anna
        • 1. Re: Need to combine 2 row values into one
          Rajesh Chamarthi
          Anna,

          What is the logic for combining these two rows? Do they belong to a particular category?
          Please provide more test data as to how you decide this.

          One similar case that I have seen is when you have primary and secondary values in two different rows and there can be only one secondary value possible (and it may or may not exist).. Like this..
          sql > create table t(
            2      id number,
            3      subject varchar2(20),
            4      prim_ind varchar2(1) default 'Y'
            5  );
          
          Table created.
          
          sql > insert into t values (100, 'Maths', 'Y');
          
          1 row created.
          
          sql > insert into t values (100, 'Science', 'N');
          
          1 row created.
          
          sql > insert into t values (200, 'Physics', 'Y');
          
          1 row created.
          
          sql > commit;
          
          Commit complete.
          
          sql > select * from t;
          
                  ID SUBJECT              P
          ---------- -------------------- -
                 100 Maths                Y
                 100 Science              N
                 200 Physics              Y
          
          select t.id,
                  t.subject primary_subject,
                  ss.subject secondary_subject
             from t,
                  (select id, subject
                      from t
                      where prim_ind = 'N') ss
            where t.id = ss.id(+)
           /
          
             ID PRIMARY_SUBJECT      SECONDARY_SUBJECT
          ----- -------------------- --------------------
            100 Science              Science
            100 Maths                Science
            200 Physics
          Another case is when you want all the employees of a particular department as one row along with the department record.

          You can use string aggregation functions in that case.

          http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

          Please provide more sample data and scenarios so that you can get the right solution for your requirement


          Thanks,
          Rajesh.

          Edited by: Rajesh Chamarthi on Dec 28, 2009 6:10 PM (added example).
          • 2. Re: Need to combine 2 row values into one
            Hoek
            Hmz..
            Interesting question, it comes down to a 'conditional aggregate', but you give us little input.
            When posting a question, try not to forget to mention the database version you're using, use CREATE TABLE and INSERT statements, so we have a testset to work with.

            However, maybe this would work (if you're at least on 10g):
            SQL> select * from t;
            
            COL1
            ----------------
            Ext-Stu
            Shortterm
            Internal student
            Foreign Student
            
            4 rows selected.
            
            SQL> select * from the_other_table;
            
            no rows selected
            
            SQL> insert into the_other_table (the_other_column)
              2  select col2
              3  from ( select col1
              4         ,      ltrim(replace(sys_connect_by_path(col1, ','), ',', chr(32)), chr(32)) col2
              5         ,      rn
              6         from ( select col1
              7                ,      case col1
              8                         when 'Ext-Stu' then 1
              9                         when 'Shortterm' then 2
             10                       end rn
             11                from   t
             12               )
             13         where connect_by_isleaf=1      
             14         connect by rn = prior rn+1
             15       )
             16  where case
             17          when col1 != col2 and rn is not null then 1
             18          when col1  = col2 and rn is null then 1
             19        end = 1;
            
            3 rows created.
            
            SQL> select * from the_other_table;
            
            THE_OTHER_COLUMN
            --------------------------------------------------------------------------------------------------
            Ext-Stu Shortterm
            Internal student
            Foreign Student
            
            3 rows selected.