Oracle generate Data

Question

I tried to generate data using the "by level" expression.

I want to generate GROUPNR, starting with 1, meanwhile each group contains 4 items with nr 1 to 4

This is, how the result should look like 

groupnr itemnr
1       1
1       2
1       3
1       4
2       1
2       2
2       3
2       4
3       1

I used this statement, but I have no idea how to handle the nvl2 or other functions to get the right values

select level, floor(level+3/4) GROUPNR, nvl2(0, mod(level,4),4) ITEMNR
from dual
connect by level <= 25;

The result of the select is:

groupnr itemnr
1       1
1       2
1       3
1       0
2       1
2       2
2       3
2       0
4       1

Please give me a hint how to modify the level to get the right values. Bye Jochen

Aleksej · Accepted Answer · 2018-07-19T11:03:52.273

This could be a way:

select floor((level -1) / 4) +1 as groupNR,
       row_number() over (partition by floor((level -1) / 4) +1 order by level) as itemNR
from dual 
connect by level <= 25;

or even, without analytic functions:

level - 4*floor((level -1) / 4) as itemNR

TEST:

SQL> select floor((level -1) / 4) +1 as groupNR,
  2         row_number() over (partition by floor((level -1) / 4) +1 order by level) as itemNR,
  3         level - 4*floor((level -1) / 4) as itemNR_2
  4  from dual
  5  connect by level <= 25
  6  order by level;

   GROUPNR     ITEMNR   ITEMNR_2
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          3
         1          4          4
         2          1          1
         2          2          2
         2          3          3
         2          4          4
         3          1          1
         3          2          2
         3          3          3
         3          4          4
         4          1          1
         4          2          2
         4          3          3
         4          4          4
         5          1          1
         5          2          2
         5          3          3
         5          4          4
         6          1          1
         6          2          2
         6          3          3
         6          4          4
         7          1          1

25 rows selected.

score 0 · Answer 2 · answered Jul 19 '18 at 10:58

0

as varaition to Aleksejs solution (Thanks):

select floor((level +3) / 4) as groupNR, row_number() over (partition by floor((level +3) / 4) +1 order by 1) as itemNR from dual connect by level <= 25;

answered Jul 19 '18 at 10:58

user3711142

63
1
8

score 0 · Answer 3 · answered Jul 19 '18 at 12:07

Your query was very close. I suggest you change it to:

select level,
       TRUNC((LEVEL-1) / 4) + 1 AS GROUPNR,
       mod(LEVEL-1, 4) + 1 AS ITEMNR
  from dual
  connect by level <= 25

This produces:

LEVEL   GROUPNR ITEMNR
1       1       1
2       1       2
3       1       3
4       1       4
5       2       1
6       2       2
7       2       3
8       2       4
9       3       1
10      3       2
11      3       3
12      3       4
13      4       1
14      4       2
15      4       3
16      4       4
17      5       1
18      5       2
19      5       3
20      5       4
21      6       1
22      6       2
23      6       3
24      6       4
25      7       1

LEVEL won't be NULL so there's no need to fiddle around with NVL or anything like that.

Best of luck.

Oracle generate Data

3 Answers3