CODEWITHSUNDEEP
sqloracle-databasewith-statementrownum
Rohit Prasad
Rohit Prasad

Reputation: 145

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.

Upvotes: 0

Views: 892

Answers (2)

Littlefoot
Littlefoot

Reputation: 143083

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>

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You want row_numner() :

select col, row_number() over (partition by col order by col) as sequence
from table t;

You can use sub-query for further operation.

Upvotes: 0

Related Questions