Reputation: 305
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
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
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
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