How to select specific rows twice?

I want to query some rows and in the resultset I want a subset of these rows twice.

With UNION ALL I can achieve this.

Are there other ways?

You can use a join to multiplicate rows, for example on a condition.

With stackoverflow (foo, bar) with values ('one', 1), ('two', 2), ('three', 3), ('four', 4) imagine you want to have one copy of rows always, duplicate records with even bar value and maybe duplicate records for foo starting with 't'. You can join an explicit values clause conditionally:

select so.*
  from stackoverflow so
 inner
  join (values (1), (2), (3)) copies(no)
    on copies.no = 1 -- first copy always
    or (copies.no = 2 and so.bar % 2 = 0) -- a copy for even bar records
    or (copies.no = 3 and so.foo like 't%') -- a copy for foo starting with 't'

you'll get one row for 1, three rows for 2 (because it's even and starts with t), two rows for 3 (because starts with t) and two rows for 4 (because even).

dbfiddle.uk live demo

Let's say we have this table:

create table my_table(id int, name varchar(10));
insert into my_table values (1, 'Kenny'), (2, 'Eric'), (3, 'Stan'), (4, 'Kyle');

Eric is bigger, he needs three rows, so let's make them recursive way:

with rec(id, name, copy) as (
  select id, name, 1 from my_table union all
  select id, name, copy + 1 from rec where name = 'Eric' and copy < 3)
select id, name, copy from rec 

dbfiddle demo