CODEWITHSUNDEEP
sqloracle11g
Floppy Retop
Floppy Retop

Reputation: 13

How to duplicate each row in sql query?

Suppose we have a query

SELECT * FROM my_table ORDER BY id

which results in

id | title
-----------
 1 | 'ABC'
 2 | 'DEF'
 3 | 'GHI'

How could I modify given select statement to have each row duplicated in the result set like this:

id | title
-----------
 1 | 'ABC'
 1 | 'ABC'
 2 | 'DEF'
 2 | 'DEF'
 3 | 'GHI'
 3 | 'GHI'

Upvotes: 0

Views: 2660

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use union all, but I like using cross join:

select *
from MyTable cross join
     (select 1 from dual union all select 2 from dual) n
order by id;

The reason I like the cross join is in the case where MyTable is really some complicated subquery. Although the query optimizer might evaluate it only once, you can't really depend on that fact. So the performance should be better in this case.

Upvotes: 2

DrabJay
DrabJay

Reputation: 3099

You could cross join to a row generator, the numeric value indicates how many duplicates per original you want.

select *
from my_table 
     cross join
       (select null
        from dual
        connect by level <= 2)
order by id

Upvotes: 0

Brendan
Brendan

Reputation: 1247

Try this...

 SELECT * FROM my_table 
 UNION ALL
 SELECT * FROM my_table
 ORDER BY id

Upvotes: 2

Related Questions