Generating Duplicate Data Series

Motif 0
Viewed 32

This example I am trying to generate and add column 1 to 5 as many number as I want. Can I solve with "Connect By" function or another function?

SELECT level
FROM   dual
CONNECT BY level <=5;

  ID   Name   Expected Outcome
-----   ----  ---------------
  1     | A  |  1
  2     | B  |  2
  3     | C  |  3
  4     | D  |  4
  5     | E  |  5
  6     | F  |  1
  7     | G  |  2
  8     | G  |  3
  9     | A  |  4
  10    | E  |  5
  11    | E  |  1
  12    | E  |  2
oracle11g oracle sql
1 Answers

Use the MOD function:

SELECT MOD(level - 1,5) + 1 
FROM   dual
CONNECT BY level <=20;

1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
Koen Lostrie0
Related