9 Replies Latest reply: Mar 6, 2011 10:02 PM by Nimish Garg RSS

    Column To Row Conversion

    km*763544*87
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        DOH! :(
                        • 9. Re: Column To Row Conversion
                          Nimish Garg
                          this may be helpful
                          http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows_16.html