I have two table A and B
C1 ---- 11 232 454 65 78 87 45 87 98 99
C2 ---- c1 c2 c3 c4
How can I join these table to get output like
C1, C2 -------- 11 ,c1 232 ,c2 454 ,c3 65 ,c4 78 ,c1 87 ,c2 45 ,c3 87 ,c4 98 ,c1 99 ,c2
Since the tables have no column in common, we'll have to use the
rownum pseudocolumn which is asigned to every row in a resultset. Do not confuse with rowid which is hash-like and exists phisycally in the table. Since rowids go 1,2,3...n, they provide a way to join two unrelated tables.
select t1.c1, t2.c2 from (select rownum rn,c1 from a) t1 left join (select rownum rn,c2 from b) t2 on (t1.rn=t2.rn)
I assumed A had more rows than B so I made a left join. You can turn things around should B have more rows than A.
Below query seems to work,If anybody has better solution please comment.
select c1,c2 from (select mod(rownum,(select count(1) from r2))+1 rn,customer_Key as c1 from r1) t1, (select rownum rn,customer_Key as c2 from r2) t2 where t1.rn=t2.rn
I would take the modulus of the row numbers, using a 0 base instead of the default 1. This example assumes we know A contains more rows than B. A window function is used to pull the maximum row number with one logical read rather than calling COUNT(*) separately.
WITH a_ AS (SELECT c1, ROWNUM - 1 AS rn, MAX(ROWNUM) OVER(PARTITION BY 1) AS max_rn FROM a), b_ AS (SELECT c2, ROWNUM - 1 AS rn, MAX(ROWNUM) OVER(PARTITION BY 1) AS max_rn FROM b) SELECT a_.c1, b_.c2 FROM a_ INNER JOIN b_ ON b_.rn = MOD(a_.rn, b_.max_rn) ORDER BY a_.rn ASC;