sqloracle-databasewith-statementrownum

How to generate repeating numbers in sequence

I would like to generate this sequence 

col sequence 
  01 01
  01 02
  01 03 
  01 04
  01 05
  02 01
  02 02
  02 03
  02 04
  02 05
  ..
  ..
  12 01
  12 02
  12 03
  12 04
  12 04

And add this as a sequence to another select.

Solution

  • Hierarchical query might help to produce result you posted:

    SQL> with
  2  c1 as
  3    (select lpad(1 + level - 1, 2, '0') col1
  4     from dual
  5     connect by level <= 12
  6    ),
  7  c2 as
  8    (select lpad(1 + level - 1, 2, '0') col2
  9     from dual
 10     connect by level <= 5
 11    )
 12  select c1.col1, c2.col2
 13  from c1 cross join c2
 14  order by c1.col1, c2.col2;

CO CO
-- --
01 01
01 02
01 03
01 04
01 05
02 01
02 02
02 03
02 04
02 05
03 01
03 02
<snip>
11 04
11 05
12 01
12 02
12 03
12 04
12 05

60 rows selected.

SQL>