CODEWITHSUNDEEP
sqloracle-database
Kesav Lenka
Kesav Lenka

Reputation: 5

Create a duplicate row on top of Select statement

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

Upvotes: 0

Views: 571

Answers (5)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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

Upvotes: 0

Serg
Serg

Reputation: 22811

One more option is LATERAL

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

Upvotes: 0

Pugzly
Pugzly

Reputation: 934

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
   /

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142723

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>

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

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;

Upvotes: 1

Related Questions