6 Replies Latest reply: Feb 7, 2013 6:14 AM by jeneesh RSS

    HOW TO COMBINE MULTIPLE ROWS INTO SINGLE ROWS

    989728
      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
          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
            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
              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
                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
                  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
                    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;