Using SELECT to create a range of numbers or duplicate rows – Oracle SQL
Scott Hollows - 14/Sep/2016
Scott Hollows - 14/Sep/2016
[SHOWTOGROUPS=4,20]
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
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
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
[/SHOWTOGROUPS]
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 <= 5DATA
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, LEVELLEVEL 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
[/SHOWTOGROUPS]
