Home » SQL & PL/SQL » SQL & PL/SQL » How to get rows N times in a result set?
How to get rows N times in a result set? [message #446567] Tue, 09 March 2010 06:15 Go to next message
terber
Messages: 1
Registered: March 2010
Junior Member
How to get rows N times in a result set?

For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once.

I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.

But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".

How can I achieve my goal?

[Updated on: Tue, 09 March 2010 07:40] by Moderator

Report message to a moderator

Re: How to get rows N times in a result set? [message #446571 is a reply to message #446567] Tue, 09 March 2010 06:21 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One record:
SQL> select * from dept where deptno = 40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Many records:
SQL> select *
  2  from (select * from dept where deptno = 40)
  3  connect by level <= 10;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON

10 rows selected.

SQL>
Re: How to get rows N times in a result set? [message #446578 is a reply to message #446571] Tue, 09 March 2010 06:35 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take care this only works with a result set with one row.

Better use:
SQL> select ename, deptno from emp where deptno = 10;
ENAME          DEPTNO
---------- ----------
CLARK              10
KING               10
MILLER             10

3 rows selected.

SQL> with lines as (select level line from dual connect by level <= 5)
  2  select ename, deptno
  3  from emp, lines
  4  where deptno = 10
  5  /
ENAME          DEPTNO
---------- ----------
CLARK              10
KING               10
MILLER             10
CLARK              10
KING               10
MILLER             10
CLARK              10
KING               10
MILLER             10
CLARK              10
KING               10
MILLER             10
CLARK              10
KING               10
MILLER             10

15 rows selected.

Regards
Michel

[Updated on: Tue, 09 March 2010 06:35]

Report message to a moderator

Previous Topic: Preventing duplicate column value using Trigger
Next Topic: DBMS_JOB
Goto Forum:
  


Current Time: Wed Sep 28 00:37:28 CDT 2016

Total time taken to generate the page: 0.13049 seconds