CODEWITHSUNDEEP
sqldatabaseoracle-database
andreybavt
andreybavt

Reputation: 1321

How to pass number of lines in "select level from dual connect by level < "?

I have a table containing some random natural numbers, for example: 

number
-------
2
3
1

I'd like to retrieve a set from this table where each row would included as many times as the value shows, for example above It would be: 

number
-------
2
2
3
3
3
1

I was thinking about a query like this:

select t.number from table t, (select level from dual connect by level <= t.number)

but apparently it doesn't work.

How to solve it?

Upvotes: 1

Views: 948

Answers (3)

rtruszk
rtruszk

Reputation: 3922

And this is query for Oracle database:

SELECT t1.number 
FROM t t1
JOIN (SELECT LEVEL FROM dual CONNECT BY LEVEL<=(SELECT MAX(number) FROM t)) t2 
ON t2.level<= t1.number;

Upvotes: 2

Mohammed
Mohammed

Reputation: 313

This is MS-SQL:

declare @str nvarchar(max);
set @str = 'select * from table where number = 0 ';
declare @c2 int;
declare @c3 int;
with cte as (select row_number() over (order by number) as seq,number from table)   
select  @c2 = max(seq) from cte;
declare @c1 int = 1;
declare @c4 int = 1;
select @c3 = min(number) from table
while (@c1 <= @c2)
begin
while (@c4 <= @c3)
begin
set @str += ' union all select * from table where number = ' + cast (@c3 as nvarchar)
set @c4 += 1
end
set @c1 += 1;
select @c3 = number from table where number = @c3 + 1
set @c4 = 1
end
set @str += ' order by number'
execute(@str)

Upvotes: 1

Robert
Robert

Reputation: 25763

try this way:

select t.id 
from tab t
join (SELECT ROWNUM as id
FROM ALL_OBJECTS
WHERE ROWNUM<=(select max(id) from tab)) t2 on t2.id <= t.id;

I'm not sure what about performance of this query but the result is as expected.

Sql fiddle Demo

Upvotes: 1

Related Questions