OraFAQ Forum: SQL & PL/SQL » Split row into two or more rows

Home » SQL & PL/SQL » SQL & PL/SQL » Split row into two or more rows (Oracle 10G r2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Split row into two or more rows [message #633248]

Thu, 12 February 2015 22:59

mmohsinaziz
Messages: 110
Registered: May 2012

Senior Member

Dear seniors,
I need to split a row of record into multiple rows. Please find below the sample table and required output.

CREATE TABLE lots
(
  lotno  NUMBER(2),
  ps     NUMBER(2),
  wt     NUMBER(4)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

INSERT INTO lots
            (lotno, ps, wt
            )
     VALUES (10, 40, 300
            );
INSERT INTO lots
            (lotno, ps, wt
            )
     VALUES (11, 40, 200
            );
INSERT INTO lots
            (lotno, ps, wt
            )
     VALUES (12, 60, 450
            );
INSERT INTO lots
            (lotno, ps, wt
            )
     VALUES (13, 80, 680
            );
INSERT INTO lots
            (lotno, ps, wt
            )
     VALUES (14, 20, 150
            );
INSERT INTO lots
            (lotno, ps, wt
            )
     VALUES (15, 40, 375
            );
commit;

SQL> select * from lots order by lotno;

     LOTNO         PS         WT
---------- ---------- ----------
        10         40        300
        11         40        200
        12         60        450
        13         80        680
        14         20        150
        15         40        375

6 rows selected.

Required Output is


     LOTNO         PS         WT   
---------- ---------- ----------   
        10         20        150     
        10         20        150
        11         20        100
        11         20        100  
        12         20        150
        12         20        150
        12         20        150
        13         20        170
        13         20        170 
        13         20        170
        13         20        170 
        14         20        150
        15         20        187.5
        15         20        187.5

No. of rows depends on PS value. PS value will always be multiple of 20. We divide the PS by 20 and answer will be the no. of rows and wt column will also be divided by no of rows. e.g In lotno 13 the total value of PS is 80 and wt is 680. After dividing PS by 20 the result is 4. it means that there will be 4 rows and value for wt column of each row will be 170.

Please help to get the required output.

Regards
Muhammad Mohsin

Report message to a moderator

Re: Split row into two or more rows [message #633249 is a reply to message #633248]

Thu, 12 February 2015 23:31

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Dividing PS with 20 would give the count of rows you need to split, and dividing WT by that count would divide it into equal number of buckets. And then the rest is ROW GENERATOR method.

SQL> SELECT * FROM lots;

     LOTNO         PS         WT
---------- ---------- ----------
        10         40        300
        11         40        200
        12         60        450
        13         80        680
        14         20        150
        15         40        375

6 rows selected.

SQL> WITH DATA AS
  2    ( SELECT t.*, t.ps/20 ps_num, t.wt/(t.ps/20) wt_bucket FROM lots t
  3    )
  4  SELECT *
  5  FROM DATA
  6    CONNECT BY LEVEL          <= ps_num
  7  AND PRIOR lotno              = lotno
  8  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
  9  /

     LOTNO         PS         WT     PS_NUM  WT_BUCKET
---------- ---------- ---------- ---------- ----------
        10         40        300          2        150
        10         40        300          2        150
        11         40        200          2        100
        11         40        200          2        100
        12         60        450          3        150
        12         60        450          3        150
        12         60        450          3        150
        13         80        680          4        170
        13         80        680          4        170
        13         80        680          4        170
        13         80        680          4        170
        14         20        150          1        150
        15         40        375          2      187.5
        15         40        375          2      187.5

14 rows selected.

SQL>

Regards,
Lalit

Report message to a moderator