Suppose I have a SELECT query of some sort which shows me 10 rows.

Is there a way I can change this query so that it shows each row twice, giving me 20 in total?

Furthermore, can some arbitrary identifier be added to the SELECT to allow distinguishing each pair of rows?

    This should work:

    select a, b, c, 1 as pair from myTableunion allselect a, b, c, 2 as pair from myTable

      Yes you can do so with a UNION ALL. Just run.

      <Your Query> UNION ALL<Your Query>

        To elaborate on @cdaiga's answer and include a unique identifier per row...

        SET @i :=0;SELECT <your columns>, @i :=@i + 1 as num from ...UNION ALLSELECT <your columns>, @i :=@i + 1 as num from ...

        Then you'll have a num column with a consecutive sequence number. Here's an example: fiddle.

          Select columns twice with arbitrary identifier then unpivot/convert to rows using union all

          for example given

          MariaDB [sandbox]> select fb_uid,id,username from users;+--------+----+----------+| fb_uid | id | username |+--------+----+----------+| 1 | 1 | John || 2 | 2 | Jane || 3 | 3 | Ali || 6 | 6 | Bruce || 7 | 7 | Martha || 8 | 8 | Sidney || 10 | 10 | charlie || 12 | 12 | Elisa || 14 | 14 | Samantha || 15 | 15 | Hannah || 16 | 16 | Hannah || 17 | 17 | Kevin || 18 | 18 | Kevin || 19 | 19 | Ruth |+--------+----+----------+14 rows in set (0.00 sec)select fb_uid,id1 id,uname1 usernamefrom(select fb_uid,id id1,username uname1, id id2, username uname2 from users u) u1union allselect fb_uid,id2,uname2from(select fb_uid,id id1,username uname1, id id2, username uname2 from users u) u2order by id

          result

          +--------+----+----------+| fb_uid | id | username |+--------+----+----------+| 1 | 1 | John || 1 | 1 | John || 2 | 2 | Jane || 2 | 2 | Jane || 3 | 3 | Ali || 3 | 3 | Ali || 6 | 6 | Bruce || 6 | 6 | Bruce || 7 | 7 | Martha || 7 | 7 | Martha || 8 | 8 | Sidney || 8 | 8 | Sidney || 10 | 10 | charlie || 10 | 10 | charlie || 12 | 12 | Elisa || 12 | 12 | Elisa || 14 | 14 | Samantha || 14 | 14 | Samantha || 15 | 15 | Hannah || 15 | 15 | Hannah || 16 | 16 | Hannah || 16 | 16 | Hannah || 17 | 17 | Kevin || 17 | 17 | Kevin || 18 | 18 | Kevin || 18 | 18 | Kevin || 19 | 19 | Ruth || 19 | 19 | Ruth |+--------+----+----------+28 rows in set (0.00 sec)

            Your Answer

             

            By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

            Not the answer you're looking for? Browse other questions tagged or ask your own question.