1 2 3 Previous Next 44 Replies Latest reply: Jun 25, 2014 5:38 AM by Lothar Flatz RSS

    How to divide a row in a table

    acd6a2be-efda-4b11-b98e-80ece826ad23

      Hey together,

       

      i have a table row which have the value 4

       

      for example

       

      Table Hobbys

      Nr  Name Hobbys

      1   Max     4

       

      My target is to build a SELECT which result is

       

      Hobby  Name

      1         Soccer

      2         Tennis

      3         Golf

      4         .....

       

      How can i do this ?

      Much thanks for your answer.

        • 1. Re: How to divide a row in a table
          BluShadow

          Where do the values for Soccer, Tennis, Golf come from? What is the logic that's required?

           

          Please post sufficient details for people to be able to help you, including the database version you're working on...

           

          Re: 2. How do I ask a question on the forums?

          • 2. Re: How to divide a row in a table
            acd6a2be-efda-4b11-b98e-80ece826ad23

            many thanks for your fast answer.

             

            row packets

            value 4

             

            now i want a select which give me following output

             

            select packets

            from table

            .....

            output:

            packets

            1

            2

            3

            4

             

            I want to split this row.

            • 3. Re: How to divide a row in a table
              BluShadow

              Ok, so it seems that you're looking for something to generate rows based on the data.

               

              Here's an example...

               

              SQL> ed
              Wrote file afiedt.buf

                1  with t as (select 1 as id, 4 as packets from dual union all
                2             select 2 as id, 3 as packets from dual)
                3  --
                4  -- end of test data - use query below on own data
                5  --
                6  select id
                7        ,level as packet
                8  from   t
                9  connect by id = prior id
              10         and level <= packets
              11*        and prior sys_guid() is not null
              SQL> /

               

                      ID     PACKET
              ---------- ----------
                       1          1
                       1          2
                       1          3
                       1          4
                       2          1
                       2          2
                       2          3

               

              7 rows selected.

               

              • 4. Re: How to divide a row in a table
                BluShadow

                Same could be achieved with recursive subquery factoring (recursive WITH clause) if you're on 11gR2 or above...

                 

                SQL> ed
                Wrote file afiedt.buf

                  1  with t as (select 1 as id, 4 as packets from dual union all
                  2             select 2 as id, 3 as packets from dual)
                  3  --
                  4  -- end of test data - use query below on own data
                  5  --
                  6      ,r(id,packets,packet) as
                  7        (select id, packets, 1 as packet from t where packets >= 1
                  8         union all
                  9         select r.id
                10               ,r.packets
                11               ,r.packet+1 as packet
                12         from   r
                13         where  r.packet+1 <= r.packets
                14        )
                15  select id
                16        ,packet
                17  from   r
                18* order by id, packet
                SQL> /

                 

                        ID     PACKET
                ---------- ----------
                         1          1
                         1          2
                         1          3
                         1          4
                         2          1
                         2          2
                         2          3

                 

                7 rows selected.

                 

                There are also other methods, such as using the MODEL clause etc. but I think that's getting too complicated for something as simple as row generation.

                • 5. Re: How to divide a row in a table
                  acd6a2be-efda-4b11-b98e-80ece826ad23

                  Sorry but i cant track your result.

                   

                  My problem is that i have one column with the value 4

                  for example:  column name "Hobby" ,  Value  "4"

                   

                  now i want to split this row in

                   

                  Hobby

                  1

                  2

                  3

                  4

                   

                  with one select. Not a function, Method or procedure

                  • 6. Re: How to divide a row in a table
                    KayK

                    Hi,

                    sorry, but what content does your table have ?

                     

                    Has "Max" 4 different hobbies or have he only hobby # 4 or is 4 the maximum value of his hobbies count from 1 to max ?

                     

                    regards
                    Kay

                    • 7. Re: How to divide a row in a table
                      BluShadow

                      That's what my queries show you.

                       

                      I've demonstrated it for a couple of "id's" each with a different number of packets/hobbies.  Each of those id's is then split into multiple rows based on how many packets/hobbies that id has.

                       

                      Perhaps it's clearer if I show it like this (same thing really)...

                       

                       

                      SQL> select * from t;

                       

                              ID      HOBBY
                      ---------- ----------
                               1          4

                       

                      SQL> select id, level as hobby
                        2  from   t
                        3  connect by id = prior id
                        4         and level <= hobby
                        5         and prior sys_guid() is not null
                        6  /

                       

                              ID      HOBBY
                      ---------- ----------
                               1          1
                               1          2
                               1          3
                               1          4

                       

                      SQL> insert into t (id, hobby) values (2, 6);

                       

                      1 row created.

                       

                      SQL> select id, level as hobby
                        2  from   t
                        3  connect by id = prior id
                        4         and level <= hobby
                        5         and prior sys_guid() is not null
                        6  /

                       

                              ID      HOBBY
                      ---------- ----------
                               1          1
                               1          2
                               1          3
                               1          4
                               2          1
                               2          2
                               2          3
                               2          4
                               2          5
                               2          6

                       

                      10 rows selected.

                      • 8. Re: How to divide a row in a table
                        acd6a2be-efda-4b11-b98e-80ece826ad23

                        No i want to have following output

                         

                        column: hobby

                        value: 4

                         

                        output

                        Hobby   Count

                        1             5

                        2             6

                        3             20

                        4             45


                        But it doesent matter what stands in the column count. I only want to split the column hobby in 1,2,3,4. How can i do this ?

                        • 9. Re: How to divide a row in a table
                          BluShadow

                          acd6a2be-efda-4b11-b98e-80ece826ad23 wrote:

                           

                          No i want to have following output

                           

                          column: hobby

                          value: 4

                           

                          output

                          Hobby   Count

                          1             5

                          2             6

                          3             20

                          4             45


                          But it doesent matter what stands in the column count. I only want to split the column hobby in 1,2,3,4. How can i do this ?

                           

                          What? Where are these counts coming from? What do they relate to?

                           

                          We don't have your database or data to know what you're asking.

                           

                          READ: Re: 2. How do I ask a question on the forums?

                           

                          and post sufficent example data and expected output, along with the logic, so that people can understand exactly what you're wanting.

                          At the moment you're posting expected output from some data we cannot see, and that is just wasting our time trying to help you.

                          • 10. Re: How to divide a row in a table
                            acd6a2be-efda-4b11-b98e-80ece826ad23

                            SQL> select id, level as hobby
                              2  from   t
                              3  connect by id = prior id
                              4         and level <= hobby
                              5         and prior sys_guid() is not null
                              6  /

                             

                                    ID      HOBBY
                            ---------- ----------
                                     1          1
                                     1          2
                                     1          3
                                     1          4

                             

                            that looks good, but i havent got a column like id or something else. So what can i do?

                             

                            select hobby ,

                                      count(player) player

                            from hobbytab

                            group by wgp.paket_nr

                            order by wgp.paket_nr;

                            that is my select. But my output looks like this one here

                             

                            hobby  player

                            1           5

                            2           3

                            4           10

                            5           12

                            But i miss the hobby with the number 3 because there is no player ( 0 ).

                            • 11. Re: How to divide a row in a table
                              BluShadow

                              What can you do? Well, you can start by posting an example of the source table.  Now you're indicating there are "players".

                               

                              We cannot help if you don't post some suitable example source data.  It's pointless just posting expected output or the 'wrong' results you're getting without us knowing what the source looks like.

                               

                              (hint: people can get sick of asking over and over again for this information!)

                              • 12. Re: How to divide a row in a table
                                acd6a2be-efda-4b11-b98e-80ece826ad23

                                select id, level as hobby

                                  2  from   t

                                  3  connect by id = prior id

                                  4         and level <= hobby

                                  5         and prior sys_guid() is not null

                                  6  /

                                 

                                        ID      HOBBY
                                ---------- ----------
                                         1          1
                                         1          2
                                         1          3
                                         1          4

                                 

                                how can i do that without the ID ? only column hobby should be split

                                • 13. Re: How to divide a row in a table
                                  BluShadow

                                  SQL> select * from t;

                                          ID      HOBBY
                                  ---------- ----------
                                           1          4

                                   

                                  SQL> select level as hobby from t connect by level <= hobby;

                                       HOBBY
                                  ----------
                                           1
                                           2
                                           3
                                           4

                                   

                                  If that's not what you want then you're out of luck.

                                  After asking several times for you to post the relevant information we need to help, you haven't bothered, so why should we bother to help?

                                  • 14. Re: How to divide a row in a table
                                    acd6a2be-efda-4b11-b98e-80ece826ad23

                                    Sorry when i execute the select i can wait many many hours until its done.

                                    I try to explain the case

                                     

                                    i have two tables. 

                                     

                                    First Table: Hobby

                                    Second Table: Player

                                    My output should look like this one

                                     

                                    Hobby.ident     Player.number

                                    1                                   2

                                    2                                   4

                                    3                                   5

                                    4                                   6

                                    The user can see how many players have the hobby with the id 1 or 2 etc. But the problem is that the column in Hobby.ident has just the number 4. Number 4 means it gives 4 Hobbys. I hope i could explain it better now.

                                    1 2 3 Previous Next