All Forums General
ksaikrishna 26 posts Joined 08/11
27 Jun 2012
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

ulrich 816 posts Joined 09/09
27 Jun 2012
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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ocirej23 10 posts Joined 04/11
29 Jun 2012

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

You must sign in to leave a comment.