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

Armored

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;

Helbonna

+1 for cross join. With any sort of a union solution, you'll have to maintain any changes to your query twice. – Iron

Or a marginally more concise version select id, name from table1 CROSS APPLY (VALUES(1),(2)) V(n) – Quondam

Could you be very kind to clarify what the following row (select 1 as n union all select 2) n means? Especially, select 1 and select 2. – Hammon

@Hammon . . . That creates a derived table with two rows and one column. In one row, the value of the column is "1". In the other, it is "2". – Helbonna

@GordonLinoff But the OUTPUT should be 1 Ab 1 1 Ab 2 2 Cd 1 2 Cd 2 3 Ef 1 3 Ef 2. How UNION ALL makes the seconds row to be the same like the previous row. I just pull my head. Thanks in advance. – Hammon

@Hammon . . . You can use order by if you need the results in a particular order. – Helbonna

@GordonLinoff no, no, I mean why is there no 1 and 2 values? However, there is a sequential repeatable rows. – Hammon

@stepUp . . . Because the OP did not request an output like that. You can include n.n in the select, if that's what you want. – Helbonna

@GordonLinoff I do not want that, I would like to know just how it works. If it is possible, could you clarify this? – Hammon

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

Betrothal

Answer 3

Solution will be like this:

select Id, name from Table1 
union all 
select Id, name from Table1

Disaffect