| Repeat Rows based on column value (merged by CM) [message #456775] |
Wed, 19 May 2010 08:38  |
cplib
Messages: 15
Registered: March 2010
|
Junior Member |
|
|
Hi
I have a select statement
select name, address, qty
from names_table
and it returns a result such as
name|address|qty
alan,1 the street,5
bert,2 the road,3
what I want to do is create an output that takes the value in the qty column and repeates the number of rows e.g. the alan record 5 times and the bert record 3 times
name|address|qty
alan,1 the street,5
alan,1 the street,5
alan,1 the street,5
alan,1 the street,5
alan,1 the street,5
bert,2 the road,3
bert,2 the road,3
bert,2 the road,3
Can anyone suggest how I might go about acheiving this?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Repeat Rows based on column value (merged by CM) [message #456799 is a reply to message #456786] |
Wed, 19 May 2010 09:17  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Link (unless I missed it) did not mention this method:
with t as (
select 'alan' name,'1 the street' address,5 qty from dual union all
select 'bert','2 the road',3 from dual
)
select t.*
from t,
table(cast(multiset(select 1 from dual connect by level <= qty) as sys.OdciNumberList))
/
NAME ADDRESS QTY
---- ------------ -----------
alan 1 the street 5
alan 1 the street 5
alan 1 the street 5
alan 1 the street 5
alan 1 the street 5
bert 2 the road 3
bert 2 the road 3
bert 2 the road 3
8 rows selected.
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Repeat Rows based on column value (merged by CM) [message #456833 is a reply to message #456830] |
Wed, 19 May 2010 11:09 |
cplib
Messages: 15
Registered: March 2010
|
Junior Member |
|
|
Sorry for not mentioning the version.
I am reading the link provided and I understand how to create a output with a number of rows based on using the CUBE function, but cant see how I can achieve what I specifically require.
Could you perhaps point me at the specific park of the link I should be studying?
Thanks again,
|
|
|
|
| Re: Repeat Rows based on column value (merged by CM) [message #456835 is a reply to message #456833] |
Wed, 19 May 2010 11:21 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
cplib wrote on Wed, 19 May 2010 12:09Sorry for not mentioning the version.
Then use:
SQL> with t as (
2 select 'alan' name,'1 the street' address,5 qty from dual union all
3 select 'bert','2 the road',3 from dual
4 )
5 select t.*
6 from t,
7 table(cast(multiset(select '1' from dual connect by level <= qty) as sys.dbms_debug_vc2coll))
8 /
NAME ADDRESS QTY
---- ------------ ----------------------------
alan 1 the street 5
alan 1 the street 5
alan 1 the street 5
alan 1 the street 5
alan 1 the street 5
bert 2 the road 3
bert 2 the road 3
bert 2 the road 3
8 rows selected.
SQL>
SY.
[Updated on: Wed, 19 May 2010 11:22] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Repeat Rows based on column value (merged by CM) [message #688065 is a reply to message #456775] |
Tue, 15 August 2023 16:54 |
 |
M117
Messages: 1
Registered: August 2023
|
Junior Member |
|
|
Another option would be to use connect by level with cross apply.
with t as (
select 'alan' name,'1 the street' address,5 qty from dual union all
select 'bert','2 the road',3 from dual
)
select t.*
FROM t
CROSS APPLY
(select 1 from dual connect by level <= qty) tt |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
