This discussion is archived
2 Replies Latest reply: Dec 29, 2009 11:17 AM by Hoek RSS

Need to combine 2 row values into one

731362 Newbie
Currently Being Moderated
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
    636749 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points