How to generate multiple records from single record

General
Enthusiast

How to generate multiple records from single record

Hi,

Can some help me how to generate mutiple records from single record without UNION/UNION ALL.

Example:

Source:

Id   Customername    account  salary

10   krishna                 savings  5000

20   sai                        Current  6000

Target:(Output)

  seq.no          Id   Customername       account     salary

     1             10   krishna                 savings  5000

     2             10   krishna                 savings  5000

     3             10   krishna                 savings  5000

     4             10   krishna                 savings  5000

     1             20   sai                        Current  6000

     2             20   sai                        Current  6000

     3             20   sai                        Current  6000

     4             20   sai                        Current  6000

Thanks,

Saikrishna.k

2 REPLIES
Supporter

Re: How to generate multiple records from single record

select row_number() over (partition by t.id order by c.id) as seq_no, 
t.id,
t.Customername,
t.account,
t.salary
from table t
cross join
(select calendar_date - current_date as id from sys_calendar.calendar where id between 1 and 4) as c

try a cross join

Enthusiast

Re: How to generate multiple records from single record

another example I have in mind:

SELECT ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY a.id) AS seq_no,

t.id,

t.customername,

t."account",

t.salary

FROM t CROSS JOIN

    (SELECT t.id, t.customername, t."account", t.salary FROM t CROSS JOIN (SELECT * FROM t) b) a;

HTH

Cheers,

Eco