This discussion is archived
6 Replies Latest reply: Feb 7, 2013 4:14 AM by jeneesh RSS

HOW TO COMBINE MULTIPLE ROWS INTO SINGLE ROWS

989728 Newbie
Currently Being Moderated
Hi,
I have a table with the following data:

CASE-1

TABLE -X

RNO      FROM_SQN     TO_SQN     DATE
==========================================
991      9           11     2010-01-01
991      11           22     2010-01-01
991      22           33     2010-01-01
992      33           44     2010-01-01




I want to see the result data as follows:

RNO      FROM_SQN     TO_SQN     DATE
==========================================
991      9           44     2010-01-01


CASE-2

TABLE -X

RNO      FROM_SQN     TO_SQN     DATE
==========================================
991      9           11     2010-01-01
991      15           22     2010-01-01
991      22           34     2010-01-01
992      33           44     2010-01-01




I want to see the result data as follows:

RNO      FROM_SQN     TO_SQN     DATE
==========================================
991      9           11     2010-01-01
991      15           44     2010-01-01




Please help me how to achieve this using SQL.

Edited by: 986725 on Feb 7, 2013 2:36 AM
  • 1. Re: HOW TO COMBINE MULTIPLE ROWS INTO SINGLE ROWS
    jeneesh Guru
    Currently Being Moderated
    select rno,min(from_sqn) from_sqn,max(to_sqn) to_sqn,date
    from x
    group by rno,date
    Note: Dont use DATE as a column name - it is a reserved word..

    Edited by: jeneesh on Feb 7, 2013 4:01 PM
    Hope, the value RNO=992, in your sample data is a typo..If not you will have to use
    select min(rno) rno,min(from_sqn) from_sqn,max(to_sqn) to_sqn,date
    from x
    group by date
  • 2. Re: HOW TO COMBINE MULTIPLE ROWS INTO SINGLE ROWS
    989728 Newbie
    Currently Being Moderated
    hi janeesh

    thank for ur support but this problem is not solve like this...
    what i do for

    CASE-2

    TABLE -X

    RNO FROM_SQN TO_SQN DATE
    ==========================================
    991 9 11 2010-01-01
    991 15 22 2010-01-01
    991 22 34 2010-01-01
    992 33 44 2010-01-01

    I want to see the result data as follows:

    RNO FROM_SQN TO_SQN DATE
    ==========================================
    991 9 11 2010-01-01
    991 15 44 2010-01-01


    regards,
    Nasir
  • 3. Re: HOW TO COMBINE MULTIPLE ROWS INTO SINGLE ROWS
    jeneesh Guru
    Currently Being Moderated
    with x as 
    (
    select 991 rno, 9 from_sqn ,11 to_sqn ,to_date('2010-01-01','yyyy-mm-dd') dt
    from dual union all
    select 991, 15 ,22 ,to_date('2010-01-01','yyyy-mm-dd') from dual union all
    select 991, 22 ,33 ,to_date('2010-01-01','yyyy-mm-dd') from dual union all
    select 991, 33 ,44 ,to_date('2010-01-01','yyyy-mm-dd') from dual 
    ),
    x_with_group as
    (
    select rno,from_sqn,to_sqn,dt,
           sum(sm) over(partition by rno,dt order by from_sqn) sm
    from
      (
      select rno,from_sqn,to_sqn,dt,
             from_sqn-
              nvl(lag(to_sqn) over(partition by rno,dt order by from_sqn),0) sm
      from x
      )
    )
    select rno,min(from_sqn) from_sqn,max(to_sqn) to_sqn,dt
    from x_with_group
    group by rno,dt,sm
    order by rno,dt,from_sqn; 
    
    RNO FROM_SQN TO_SQN DT        
    --- -------- ------ -----------
    991        9     11 01-jan-2010 
    991       15     44 01-jan-2010 
    Edited by: jeneesh on Feb 7, 2013 4:59 PM
    Assumed the date values are actually DATE types.
    Partition on DT and RNO can be amended as per your requirement..
    And assumed your sample data has a typo..


    If your data is correct..
    with x as 
    (
    select 991 rno, 9 from_sqn ,11 to_sqn ,to_date('2010-01-01','yyyy-mm-dd') dt
    from dual union all
    select 991, 15 ,22 ,to_date('2010-01-01','yyyy-mm-dd') from dual union all
    select 991, 22 ,33 ,to_date('2010-01-01','yyyy-mm-dd') from dual union all
    select 992, 33 ,44 ,to_date('2010-01-01','yyyy-mm-dd') from dual 
    ),
    x_with_group as
    (
    select rno,from_sqn,to_sqn,dt,
           sum(sm) over(order by from_sqn) sm
    from
      (
      select rno,from_sqn,to_sqn,dt,
             from_sqn-
              nvl(lag(to_sqn) over(order by from_sqn),0) sm
      from x
      )
    )
    select min(rno) rno,min(from_sqn) from_sqn,max(to_sqn) to_sqn,min(dt) dt
    from x_with_group
    group by sm
    order by rno,dt,from_sqn; 
    
    RNO FROM_SQN TO_SQN DT        
    --- -------- ------ -----------
    991        9     11 01-jan-2010 
    991       15     44 01-jan-2010 
    Edited by: jeneesh on Feb 7, 2013 5:14 PM
  • 4. Re: HOW TO COMBINE MULTIPLE ROWS INTO SINGLE ROWS
    Purvesh K Guru
    Currently Being Moderated
    One way of doing it:
    with data as
    (
      select 991 rno, 9 frm, 11 t, to_date('2010-01-01', 'YYYY-MM-DD') dt from dual union all
      select 991, 15, 22, to_date('2010-01-01', 'YYYY-MM-DD') dt from dual union all 
      select 991, 22, 33, to_date('2010-01-01', 'YYYY-MM-DD') dt from dual union all
      select 992, 33, 44, to_date('2010-01-01', 'YYYY-MM-DD') dt from dual
    )
    select min(rno) rno, min(frm) frm, max(t) t, min(dt) dt
      from (
            select rno, frm, t, dt,
                   case 
                    when t = lead(frm, 1, t) over (partition by dt order by frm) then
                      1
                    else
                      0
                   end rnk
              from data
           )
     group by rnk, dt;
    
    RNO                    FRM                    T                      DT                        
    ---------------------- ---------------------- ---------------------- ------------------------- 
    991                    9                      11                     01-Jan-2010               
    991                    15                     44                     01-Jan-2010
  • 5. Re: HOW TO COMBINE MULTIPLE ROWS INTO SINGLE ROWS
    989728 Newbie
    Currently Being Moderated
    thanks a lot for ur cooperation...

    how to make sql for all data have similar nature

    my table have 27 lakh rno with 5.6 crore rows..
  • 6. Re: HOW TO COMBINE MULTIPLE ROWS INTO SINGLE ROWS
    jeneesh Guru
    Currently Being Moderated
    Just remove the sample data part from the queries given..
    x_with_group as
    (
    select rno,from_sqn,to_sqn,dt,
           sum(sm) over(order by from_sqn) sm
    from
      (
      select rno,from_sqn,to_sqn,dt,
             from_sqn-
              nvl(lag(to_sqn) over(order by from_sqn),0) sm
      from your_table --"use your table name"
      )
    )
    select min(rno) rno,min(from_sqn) from_sqn,max(to_sqn) to_sqn,min(dt) dt
    from x_with_group
    group by sm
    order by rno,dt,from_sqn;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points