Hi all,
I have a hierarchy between levels in my_hierarchy table (between id_lvl and id_lvl_sup).
Each level (lvl) may have items (id_item), and each item has a category (id_item_sup). These data is stored in my_items table.
Now I want to build a report based on the my_view view.
In rows, we have id_lvl1 and id_lvl2.
In columns, we have id_item_sup (1st level) and id_item (2nd level).
In cells, we have the total of items for each id_lvl2.
I want to transform the definition of my_view to "normalize" the number of items that appears in each id_item_sup.
For example, id_item_sup = 2001, we have 4 items.
ID_H ID_ITEM_SUP ORD_ITEM_SUP NB_ITEM_PER_ITEM_SUP
---------- ----------- ------------ --------------------
1000 1002 1002 11
1000 2001 2001 4
1000 2100 2100 5
1000 2200 2200 40
1000 3001 3001 25
1000 3500 3500 13
1000 4001 4001 1
1000 6001 6001 9
1000 7001 7001 3
1000 8001 8001 9
1000 0I want to "padd" each id_item_sup to reach 10 items (p_nb_item_per_grp column in the view).
The padding consists of adding items with lib_item = '----' at the last of existing items.
ID_H ID_ITEM_SUP ORD_ITEM_SUP ID_ITEM LIB_I ORD_ITEM NB
---------- ----------- ------------ ---------- ----- ---------- ----------
1000 2001 2001 2002 2002 2002 47
1000 2001 2001 2003 2003 2003 501
1000 2001 2001 2004 2004 2004 3
1000 2001 2001 2007 2007 2007 3
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0
1000 2001 2001 9999 ---- 9999 0Then we have two (02) cases :
- number of items < 10, so we pad items to reach 10 items ;
- number of items >= 10, so we create blocks of 10 items. Then, in all the blocks different from the first block we add " next" to lib_item_sup.
For example, number of items is 11, so we create two (02) blocks :
- The 1st one, with 10 items
- The 2nd one, with one (01) item padded with 9 items with lib '----', and we concatenate " next" to lib_item_sup
Another example, number of items is 34, so we create four (04) blocks :
- The 1st one, with 10 items
- The 2nd and the third one, with 10 items and we concatenate " next" to lib_item_sup
- The 4th one, with four (04) items, pad with six (06) items with lib '----', then concatenate " next" to lib_item_sup
Hope it's clear
Thanks in advance
I'm not entirely sure I follow your test case, with all of the "sups" and "items" etc.
But anyway, I've thrown together a smaller example showing how you can pad out to the nearest 10rows.
SQL> create table t ( x int , y int);
Table created.
SQL> insert into t select 1 , rownum*10 from dual connect by level <= 7;
7 rows created.
SQL> insert into t select 2 , rownum*20 from dual connect by level <= 13;
13 rows created.
SQL> insert into t select 3 , rownum*30 from dual connect by level <= 26;
26 rows created.
SQL>
SQL> select * from t order by 1,2;
X Y
---------- ----------
1 10
1 20
1 30
1 40
1 50
1 60
1 70
2 20
2 40
2 60
2 80
2 100
2 120
2 140
2 160
2 180
2 200
2 220
2 240
2 260
3 30
3 60
3 90
3 120
3 150
3 180
3 210
3 240
3 270
3 300
3 330
3 360
3 390
3 420
3 450
3 480
3 510
3 540
3 570
3 600
3 630
3 660
3 690
3 720
3 750
3 780
46 rows selected.
SQL>
SQL> with limits as
2 (
3 select x,
4 count(*) cnt
5 from t
6 group by x
7 ),
8 padding as
9 ( select x, idx
10 from limits,
11 lateral(select rownum idx from dual connect by level <= ceil(limits.cnt/10)*10 )
12 ),
13 indexed_t as
14 ( select t.*, row_number() over ( partition by x order by y) as seq
15 from t
16 )
17 select padding.x, padding.idx, indexed_t.x,indexed_t.y
18 from padding
19 left outer join indexed_t
20 on padding.x = indexed_t.x and padding.idx = indexed_t.seq
21 order by padding.x, padding.idx;
X IDX X Y
---------- ---------- ---------- ----------
1 1 1 10
1 2 1 20
1 3 1 30
1 4 1 40
1 5 1 50
1 6 1 60
1 7 1 70
1 8
1 9
1 10
2 1 2 20
2 2 2 40
2 3 2 60
2 4 2 80
2 5 2 100
2 6 2 120
2 7 2 140
2 8 2 160
2 9 2 180
2 10 2 200
2 11 2 220
2 12 2 240
2 13 2 260
2 14
2 15
2 16
2 17
2 18
2 19
2 20
3 1 3 30
3 2 3 60
3 3 3 90
3 4 3 120
3 5 3 150
3 6 3 180
3 7 3 210
3 8 3 240
3 9 3 270
3 10 3 300
3 11 3 330
3 12 3 360
3 13 3 390
3 14 3 420
3 15 3 450
3 16 3 480
3 17 3 510
3 18 3 540
3 19 3 570
3 20 3 600
3 21 3 630
3 22 3 660
3 23 3 690
3 24 3 720
3 25 3 750
3 26 3 780
3 27
3 28
3 29
3 30
60 rows selected.
SQL>
SQL>
SQL>