Create a duplicate row on top of Select statement-CodePudding

table TEST

id	Name
1	abc
2	xyz

In general i used to get records from below query Select id,name from TEST.

id	Name
1	abc
2	xyz

but now i want to create a duplicate for each row on top my select query

expected output: please suggest how can i achieve result like below

id	Name
1	abc
1	abc
2	xyz
2	xyz

CodePudding user response：

You may cross join your table with a sequence table containing how ever many copies you want. Here is an example using an inline sequence table:

SELECT t1.id, t1.Name
FROM yourTable t1
CROSS JOIN (
    SELECT 1 AS seq FROM dual UNION ALL
    SELECT 2 FROM dual UNION ALL
    SELECT 3 FROM dual
) t2
WHERE t2.seq <= 2
ORDER BY t1.id;

CodePudding user response：

To me, UNION (ALL) set operator seems to be quite simple.

Sample data:

SQL> select * from test;

        ID NAME
---------- ----
         1 abc
         2 xyz

UNION ALL:

SQL> select * from test
  2  union all
  3  select * from test;

        ID NAME
---------- ----
         1 abc
         2 xyz
         1 abc
         2 xyz

SQL>

CodePudding user response：

CREATE table test(
id integer,
name VARCHAR2(4)
);

INSERT into test (id, name) VALUES (1,'ABC');

INSERT into test (id, name) VALUES (2,'XYZ');


 with data as (select level l from dual connect by level <= 2)
    select *
      from test, data
     order by id, l
   /

CodePudding user response：

One more option is LATERAL

SELECT t.*
FROM test 
 , LATERAL (
      SELECT id, name FROM DUAL
      union all
      SELECT id, name FROM DUAL
   ) t

CodePudding user response：

One option is using a self-join along with ROW_NUMBER analytic function such as

WITH t AS
(
 SELECT t1.id, t1.name, ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY 0) AS rn
   FROM test t1,
        test t2
)
SELECT id, name
  FROM t
 WHERE rn <= 2

Demo