how to repeat each row twice

Question

I have a requirement for a report and I would like my sql query to repeat each row twice.

Example :

  **Table 1**
   Id   Name
   1     Ab
   2     Cd
   3     Ef

I want to write a query which outputs the following :

  1   Ab
  1   Ab
  2   Cd
  2   Cd
  3   Ef
  3   Ef

Is there a way I can do it ?

I cannot think of anything except using union

Select Id, name from Table1 union select Id, name from Table1

Answer 1

You can use a union all. A union will not work, because it will eliminate duplicates. Another way is a cross join:

select id, name
from table1 t1 cross join
     (select 1 as n union all select 2) n;

Answer 2

You can also use UNION ALL, put them under CTE (Common Table Expression) and Order By Id:

WITH CTE AS
( 
SELECT Id, Name FROM Table_1
    UNION ALL
SELECT Id, Name FROM Table_1
)
SELECT Id, Name
FROM CTE
ORDER BY Id;

As this will reorder them and stacked them as duplicates