Using SELECT to create a range of numbers or duplicate rows – Oracle SQL

This simple technique will show you how to write a SELECT statement that dynamically creates a number of rows.  It can be used to duplicate data or create a numbers on the fly, so the data looks like this.

DATA
1
2
3
4
5

Select a range of numbers

To dynamically create a range of numbers we can re-purpose Oracle’s CONNECT BY query that is normally usually used for selecting hierarchical data.

Like this

SELECT  LEVEL  DATA
FROM    DUAL
CONNECT BY LEVEL <= 5
DATA
1
2
3
4
5

Duplicating rows

For another example, lets say you want to duplicate your source data so each row appears multiple times in the query results

Lets make each row in the STOCK_CATEGORY table appear twice.
This is how you do it

SELECT LEVEL, S.CODE, S.NAME
FROM   DUAL  D,
       STOCK_CATEGORY S
CONNECT BY LEVEL <= 2
ORDER BY S.CODE, LEVEL
LEVEL    CODE     NAME
1        HAMMER   Stop Hammer Time
2        HAMMER   Stop Hammer Time
1        NAIL     Yeah you nailed it
2        NAIL     Yeah you nailed it

Database Nerd Notes – This is called a cartesian join where there is no join condition between the two data sources

Scott Hollows has been working with Oracle since 1991 using Oracle 6 through to 12.  He has never met a SQL statement that he cant tame

About The Author

scott_circle
The Usual Suspect
– Scott Hollows –

  • Oracle and Delphi software developer.
  • Australian Delphi User Group – Western Australia Chief Cat Herder
  • Australian Delphi User Group – President
blog email linkedinlogo

Author: Scott Hollows

Enterprise software developer based in Perth, Western Australia. Focused on Oracle, Delphi, Data Warehouse design and ETL, Data Architect, Business Intelligence, Oracle performance tuning. President of the Australian Delphi User Group (ADUG) LinkedIn www.linkedin.com/in/scotthollows

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s