This discussion is archived
9 Replies Latest reply: Mar 6, 2011 8:02 PM by Nimish Garg RSS

Column To Row Conversion

km*763544*87 Newbie
Currently Being Moderated
Hi friends,

A table contains data like this...

Table_Test
PRODUCT_ID PRODUCT_TYPE
********* ***********
1 A,B,C,D,E
2 aa,bb,cc,dd,ee
3 AB,BC,CD,DE

I need to convert the datas in this format,

Table_Test_split
PRODUCT_ID PRODUCT_TYPE
********* ***********
1 A
1 B
1 c
1 D
1 E
2 aa
2 bb
2 cc
2 dd
2 ee
3 AB
3 BC
3 CD
3 DE

i need to do this using a Procedure...

can any one give me some idea....

Edited by: km**** on Mar 5, 2011 12:18 PM
  • 1. Re: Column To Row Conversion
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Here's one way:
    WITH     got_item_cnt     AS
    (
         SELECT     product_id
         ,     product_tupe
         ,     1 + LENGTH (product_type)
                - LENGTH (REPLACE (product_type, ','))     AS item_cnt
         FROM     table_test
    )
    ,     cntr     AS
    (
         SELECT     LEVEL     AS n
         FROM     (
                   SELECT     MAX (item_cnt)     AS max_item_cnt
                   FROM     got_item-cnt
              )
         CONNECT BY     LEVEL <= max_item_cnt
    )
    SELECT       i.product_id
    ,       REGEXP_SUBSTR ( i.product_type
                          , '[^,]+'
                         , 1
                         , c.n
                         )     AS product_type
    FROM       got_item_cnt     i
    JOIN       cntr          c  ON     c.n     <= i.item_cnt
    ORDER BY  i.product_id
    ,       c.n
    ;
    If you'd care to post CREATE TABLE and INSERT statements for the sample data, then I could test this.

    There might be better ways, depending on which version of Oracle you have. The query above works in Oracle 10 (and up). What version do you have? You should always include this information whenever you have a question.

    Relational databases work best when a column on a row holds only one piece of information. It looks like you need to treat 'A,B,C,D,E' as 5 separate items, so you should probably be storing it on 5 separate rows in the first place.
    km**** wrote:
    i need to do this using a Procedure...
    Why? You don't need a procedure, and I can't see why you would want one.
    Post some pseudo-code, showing how you want to use a procedure.
  • 2. Re: Column To Row Conversion
    Solomon Yakobson Guru
    Currently Being Moderated
    There is no need for a procedure. It can be done in SQL:
    with t as (
               select 1 product_id,'A,B,C,D,E' product_type from dual union all
               select 2,'aa,bb,cc,dd,ee' from dual union all
               select 3,'AB,BC,CD,DE' from dual
              )
    -- end of on-the-fly data sample
    select  product_id,
            substr(
                   product_type,
                   instr(',' || product_type,',',1,column_value),
                   instr(product_type || ',',',',1,column_value) - instr(',' || product_type,',',1,column_value)
                  ) product_type
      from  t,
            table(
                  cast(
                       multiset(
                                select  level
                                  from  dual
                                  connect by level <= length(product_type) - length(replace(product_type,',')) + 1
                               )
                       as sys.OdciNumberList
                      )
                 )
      order by product_id,
               column_value
    /
    
    PRODUCT_ID PRODUCT_TYPE
    ---------- --------------
             1 A
             1 B
             1 C
             1 D
             1 E
             2 aa
             2 bb
             2 cc
             2 dd
             2 ee
             3 AB
    
    PRODUCT_ID PRODUCT_TYPE
    ---------- --------------
             3 BC
             3 CD
             3 DE
    
    14 rows selected.
    
    SQL> 
    SY.
  • 3. Re: Column To Row Conversion
    MichaelS Guru
    Currently Being Moderated
    Or
    SQL> with t as (
     select 1 product_id,'A,B,C,D,E' product_type from dual union all
     select 2,'aa,bb,cc,dd,ee' from dual union all
     select 3,'AB,BC,CD,DE' from dual
    )
    --
    --
    select product_id, column_value product_type
      from (select product_id, '"' || replace (product_type, ',', '","') || '"' str 
              from t), 
            xmltable (str)
    /
         PRODUCT_ID PRODUCT_TYPE
    --------------- ------------
                  1 A           
                  1 B           
                  1 C           
                  1 D           
                  1 E           
                  2 aa          
                  2 bb          
                  2 cc          
                  2 dd          
                  2 ee          
                  3 AB          
                  3 BC          
                  3 CD          
                  3 DE          
    
    14 rows selected.
  • 4. Re: Column To Row Conversion
    750281 Newbie
    Currently Being Moderated
    by watching you and solomon sir sql query really hat's off to you... good job... quite advanced you all...
    because of watching such an advanced query now a days i always try to use first sql instead of pl/sql..

    really heavy power in sql query...

    thanks
  • 5. Re: Column To Row Conversion
    Peter Gjelstrup Guru
    Currently Being Moderated
    Hi FRNzzz!! wrote:
    because of watching such an advanced query now a days i always try to use first sql instead of pl/sql..
    Good choice :)
  • 6. Re: Column To Row Conversion
    FlyingGuy Explorer
    Currently Being Moderated
    You have some great replies here.

    What you should do however is normalize your data.

    spreadsheet != database and I don't care how many people think it is. Storing the same type of data in ever increasing numbers of columns will always lead to poor performance and having to write ridiculous queries.
  • 7. Re: Column To Row Conversion
    Peter Gjelstrup Guru
    Currently Being Moderated
    What you should do however is normalize your data.
    Yes. In which case he needs what he is asking; A way to normalize it ;)

    Regards
    Peter
  • 8. Re: Column To Row Conversion
    FlyingGuy Explorer
    Currently Being Moderated
    DOH! :(
  • 9. Re: Column To Row Conversion
    Nimish Garg Guru
    Currently Being Moderated
    this may be helpful
    http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows_16.html

Legend

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