DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Query Help - Merging two rows

  1. #1
    Join Date
    Oct 2013
    Posts
    4

    Query Help - Merging two rows

    Hi,

    PFB the sample data.

    CREATE TABLE "MY_TABLE"
    ("NBR" VARCHAR2(20 BYTE),
    "MO_YR" NUMBER,
    "EFF_DT" DATE
    ) ;

    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('101',201310,Sysdate-20);
    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('102',201310,Sysdate-15);
    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('103',201310,Sysdate-10);
    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('104',201310,Sysdate-5);
    INSERT INTO MY_TABLE (NBR, MO_YR, EFF_DT) VALUES('105',201310,Sysdate);
    COMMIT;
    SELECT * FROM MY_TABLE;

    Here, i need to get a dataset like this.

    Prev. | Old | Eff DT
    105 104 Sysdate
    104 103 Sysdate-5
    103 102 Sysdate-10
    102 101 Sysdate-15

    As you can see, i need to merge the two consecutive records in the above fashion. Thanks in advance.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    .
    Try using the one of these analytic functions: LAG or LEAD.

    Perhaps you could learn about analytic functions if you Read This Fine Manual:
    Oracle® Database SQL Language Reference
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Oct 2013
    Posts
    4
    Quote Originally Posted by LKBrwn_DBA View Post
    .
    Try using the one of these analytic functions: LAG or LEAD.

    Perhaps you could learn about analytic functions if you Read This Fine Manual:
    Oracle® Database SQL Language Reference
    Thank you Good sir,
    I know most of these functions and use them often. But, this requirement of mine is lil twisted and i just can not figure out how to do it. Anyways, thanks for writing in.

    Hoping for some help on this.

    -Parthiban

  4. #4
    Join Date
    Oct 2013
    Posts
    4
    NBR AS CURR_NBR,
    LAG(NBR, 1, 0) OVER (PARTITION BY MO_YR
    ORDER BY MO_YR, EFF_DT) AS PREV_NBR.

    Thanks

  5. #5
    Join Date
    Oct 2013
    Posts
    4
    Sorry i clicked report button by mistake.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by ParthibanSekar View Post
    Sorry i clicked report button by mistake.
    I'm not sure if that feature even works...

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Yep, you got it:
    Code:
    SQL> WITH my_table (nbr, mo_yr, eff_dt)
      2    AS (SELECT '101', 201310, TRUNC ( SYSDATE - 20) FROM DUAL UNION ALL
      3        SELECT '102', 201310, TRUNC ( SYSDATE - 15) FROM DUAL UNION ALL
      4        SELECT '103', 201310, TRUNC ( SYSDATE - 10) FROM DUAL UNION ALL
      5        SELECT '104', 201310, TRUNC ( SYSDATE -  5) FROM DUAL UNION ALL
      6        SELECT '105', 201310, TRUNC ( SYSDATE     ) FROM DUAL)
      7  SELECT nbr AS curr_nbr
      8       , LAG ( nbr, 1, 0)
      9           OVER ( PARTITION BY mo_yr ORDER BY mo_yr, eff_dt) AS prev_nbr
     10       , eff_dt
     11    FROM my_table
     12*  ORDER BY eff_dt desc
    SQL> /
    
    CURR_NBR  PREV_NBR  EFF_DT
    --------- --------- -----------------------------
    105       104       28-Oct-2013 00:00:00
    104       103       23-Oct-2013 00:00:00
    103       102       18-Oct-2013 00:00:00
    102       101       13-Oct-2013 00:00:00
    101         0       08-Oct-2013 00:00:00
    
    SQL>
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width