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

# How to divide a row in a table

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 ?

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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