CODEWITHSUNDEEP
sqloracle-database
siddharth gupta
siddharth gupta

Reputation: 305

Simplest way to repeat every N rows in sql

I know this is possible through some complex techniques, i want to know any simplest way to achieve this patter that every 10 rows should repeat . for example 

        select a,b from tablename; (repeating 2 for example)

will give 

        a1,b1     
        a2,b2
        a1,b1  
        a2.b2
        a3,b3
        a4,b4
        a3,b3
        a4,b4

where if it was 10 it will go like 

    a1,b1 to a10,b10 again a1,b1 to a10,b10

then

    a11,b11 to a20,b20 again a11,b11 to a20,b20

and so on

Upvotes: 0

Views: 1569

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want blocks of ten rows repeated twice. So to get:

rows 1 to 10
rows 1 to 10
rows 11 to 20
rows 11 to 20
...

In order to get rows n-fold cross join with a table holding n records. (You get such for instance by querying a big enough table and stop at rowcount n.)

You also need the row number of your original records, so you can get block 1 first, then block 2 and so on. Use integer division to get from row numbers to blocks.

select t.a, t.b
from (select a, b, row_number() over (order by a, b) as rn from tablename) t
cross join (select rownum as repeatno from bigenoughtable where rownum <= 2) r
order by trunc((t.rn -1) / 10), r.repeatno, t.a, t.b;

Upvotes: 2

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

I would rather not use UNION so many times. My way would be CONNECT BY ROWNUM <=N. Actually a CARTESIAN JOIN. So, basically you need a ROW GENERATOR to cartesian join with it.,

Update

For example, this will repeat 10 rows 2 times -

SQL> WITH t AS
  2    ( SELECT 'a1' A, 'b1' b FROM dual
  3    UNION ALL
  4    SELECT 'a2' a, 'b2' b FROM dual
  5    UNION ALL
  6    SELECT 'a3' a, 'b3' b FROM dual
  7    UNION ALL
  8    SELECT 'a4' A, 'b4' b FROM dual
  9    UNION ALL
 10    SELECT 'a5' A, 'b5' b FROM dual
 11    UNION ALL
 12    SELECT 'a6' a, 'b6' b FROM dual
 13    UNION ALL
 14    SELECT 'a7' A, 'b7' b FROM dual
 15    UNION ALL
 16    SELECT 'a8' a, 'b8' b FROM dual
 17    UNION ALL
 18    SELECT 'a9' a, 'b9' b FROM dual
 19    UNION ALL
 20    SELECT 'a10' a, 'b10' b FROM dual
 21    )
 22  SELECT A,B FROM t,
 23    (SELECT 1 FROM DUAL CONNECT BY ROWNUM <=2
 24    )
 25    /

A   B
--- ---
a1  b1
a2  b2
a3  b3
a4  b4
a5  b5
a6  b6
a7  b7
a8  b8
a9  b9
a10 b10
a1  b1
a2  b2
a3  b3
a4  b4
a5  b5
a6  b6
a7  b7
a8  b8
a9  b9
a10 b10

20 rows selected.

SQL>

So, above CONNECT BY ROWNUM <=10 means repeat the rows 10 times. If you want it to be repeated N times use CONNECT BY ROWNUM <=N.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use a CTE and union all:

with rows as (
      select a, b
      from tablename
      where rownum <= 2
     )
select *
from rows
union all
select *
from rows;

Just some caveats to this. You should use an order by if you want particular rows from the table. This is important, because the same select can return different sets of rows. Actually, considering this, a better way is probably:

with rows as (
      select a, b
      from tablename
      where rownum <= 2
     )
select *
from rows cross join
     (select 1 as n from dual union all select 2 from dual) n;

Upvotes: 2

Related Questions