2 Replies Latest reply on Dec 29, 2009 7:17 PM by Hoek

    Need to combine 2 row values into one

      The existing table values look like

      Column 1
      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

        • 1. Re: Need to combine 2 row values into one
          Rajesh C

          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(+)
          ----- -------------------- --------------------
            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.


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


          Edited by: Rajesh Chamarthi on Dec 28, 2009 6:10 PM (added example).
          • 2. Re: Need to combine 2 row values into one
            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;
            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;
            Ext-Stu Shortterm
            Internal student
            Foreign Student
            3 rows selected.