I need to duplicate the sorted rows in each group -"n_group" so that the output is 5 rows in each group. The group should be filled with the values ​​of the previous rows of the group.

Input table:

Desired Results:

CodePudding user response：

Here's one option - it uses hierarchical query. See if it helps.

Sample data:

SQL> with test (n_group, n_sort, n_name) as
  2    (select 'aa', 1, 'pr_1' from dual union all
  3     select 'bb', 1, 'pr_1' from dual union all
  4     select 'bb', 2, 'pr_2' from dual union all
  5     select 'cc', 1, 'pr_4' from dual union all
  6     select 'cc', 2, 'pr_8' from dual union all
  7     select 'cc', 3, 'pr_9' from dual
  8    ),

Query begins here; multiply existing rows as many times as line #15 returns; row_number analytic function's result is later used to fetch only the first 5 rows per each n_group.

  9  temp as
 10    (select t.n_group, t.n_sort, t.n_name,
 11       row_number() over (partition by n_group order by n_sort, n_name) rn
 12     from test t cross join
 13       table(cast(multiset(select level from dual
 14                           connect by level <=
 15                             (select 5 - case when max(b.n_sort) = 1 then 0 else max(b.n_sort) end
 16                              from test b
 17                              where b.n_group = t.n_group)
 18                             ) as sys.odcinumberlist))
 19    )
 20  select n_group, n_sort, n_name
 21  from temp
 22  where rn <= 5
 23  order by n_group, n_sort, n_name;

Result:

N_GROUP        N_SORT N_NAME
---------- ---------- ----------
aa                  1 pr_1
aa                  1 pr_1
aa                  1 pr_1
aa                  1 pr_1
aa                  1 pr_1
bb                  1 pr_1
bb                  1 pr_1
bb                  1 pr_1
bb                  2 pr_2
bb                  2 pr_2
cc                  1 pr_4
cc                  1 pr_4
cc                  2 pr_8
cc                  2 pr_8
cc                  3 pr_9

15 rows selected.

CodePudding user response：

You can use a PARTITIONed join to a row-generator function:

SELECT n_group, n_sort, n_name
FROM   ( SELECT LEVEL AS n FROM DUAL CONNECT BY LEVEL <= 5 ) l
       INNER JOIN (
         SELECT t.*,
                COUNT(*) OVER (PARTITION BY n_group) AS n_rows
         FROM   table_name t
       ) t
       PARTITION BY (t.n_group)
       ON (MOD(l.n - 1, t.n_rows)   1 = t.n_sort)
ORDER BY t.n_group, l.n;

Which, for the sample data:

CREATE TABLE table_name (n_group, n_sort, n_name) AS
SELECT 'aa', 1, 'pr_1' FROM DUAL UNION ALL
SELECT 'bb', 1, 'pr_1' FROM DUAL UNION ALL
SELECT 'bb', 2, 'pr_2' FROM DUAL UNION ALL
SELECT 'cc', 1, 'pr_4' FROM DUAL UNION ALL
SELECT 'cc', 2, 'pr_8' FROM DUAL UNION ALL
SELECT 'cc', 3, 'pr_9' FROM DUAL;

Outputs:

N_GROUP	N_SORT	N_NAME
aa	1	pr_1
aa	1	pr_1
aa	1	pr_1
aa	1	pr_1
aa	1	pr_1
bb	1	pr_1
bb	2	pr_2
bb	1	pr_1
bb	2	pr_2
bb	1	pr_1
cc	1	pr_4
cc	2	pr_8
cc	3	pr_9
cc	1	pr_4
cc	2	pr_8

db<>fiddle here