This discussion is archived
3 Replies Latest reply: Jan 4, 2013 2:28 AM by 908002 RSS

Query

977863 Newbie
Currently Being Moderated
Hi all,
i am using oracle 11.2.0.3.0 Below is my table

select 123 col1,'x' col2,'y' col3,100 col4 from dual union all
select 123 col1,'x1' col2,'y' col3,200 col4 from dual union all
select 123 col1,'x' col2,'z1' col3,300 col4 from dual union all
select 522 col1,'xx' col2,'yy' col3,200 col4 from dual union all
select 522 col1,'yy' col2,'zz' col3,100 col4 from dual.


output i need is
col1 col2 col3 col4
123 x y 100
x1 y 200
x z1 300
522 xx yy 200
yy zz 100
  • 1. Re: Query
    jeneesh Guru
    Currently Being Moderated
    In SQL Plus use BREAK
    SQL> break on col1;
    SQL> with t as
      2  (
      3  select 123 col1,'x' col2,'y' col3,100 col4 from dual union all
      4  select 123 col1,'x1' col2,'y' col3,200 col4 from dual union all
      5  select 123 col1,'x' col2,'z1' col3,300 col4 from dual union all
      6  select 522 col1,'xx' col2,'yy' col3,200 col4 from dual union all
      7  select 522 col1,'yy' col2,'zz' col3,100 col4 from dual
      8  )
      9  select *
     10  from t
     11  ;
    
          COL1 CO CO       COL4
    ---------- -- -- ----------
           123 x  y         100
               x1 y         200
               x  z1        300
           522 xx yy        200
               yy zz        100
    Or
    SQL> with t as
      2  (
      3  select 123 col1,'x' col2,'y' col3,100 col4 from dual union all
      4  select 123 col1,'x1' col2,'y' col3,200 col4 from dual union all
      5  select 123 col1,'x' col2,'z1' col3,300 col4 from dual union all
      6  select 522 col1,'xx' col2,'yy' col3,200 col4 from dual union all
      7  select 522 col1,'yy' col2,'zz' col3,100 col4 from dual
      8  ),
      9  ordered_data as
     10  (
     11  select row_number() over(partition by col1 order by col2,col3,col4) rn,
     12         col1,col2,col3,col4
     13  from t
     14  )
     15  select decode(rn,1,col1) column1,col2,col3,col4
     16  from ordered_data
     17  order by col1,rn;
    
       COLUMN1 CO CO       COL4
    ---------- -- -- ----------
           123 x  y         100
               x  z1        300
               x1 y         200
           522 xx yy        200
               yy zz        100
    Edited by: jeneesh on Jan 4, 2013 3:57 PM
  • 2. Re: Query
    908002 Expert
    Currently Being Moderated
     with t as
        (
       select 123 col1,'x' col2,'y' col3,100 col4 from dual union all
       select 123 col1,'x1' col2,'y' col3,200 col4 from dual union all
        select 123 col1,'x' col2,'z1' col3,300 col4 from dual union all
        select 522 col1,'xx' col2,'yy' col3,200 col4 from dual union all
        select 522 col1,'yy' col2,'zz' col3,100 col4 from dual
        )
        select decode(col1,lag(col1) over (order by col1) , null, col1) col1, col2,col3, col4
       from t
  • 3. Re: Query
    ranit B Expert
    Currently Being Moderated
    Something like this...
    with xx as(
        select 123 col1,'x' col2,'y' col3,100 col4 from dual union all
        select 123 col1,'x1' col2,'y' col3,200 col4 from dual union all
        select 123 col1,'x' col2,'z1' col3,300 col4 from dual union all
        select 522 col1,'xx' col2,'yy' col3,200 col4 from dual union all
        select 522 col1,'yy' col2,'zz' col3,100 col4 from dual
    )
    select decode(rn,1,col1), col2, col3, col4
    from(
        SELECT 
            col1, col2, col3, col4,
            row_number() over(partition by col1 order by col1,col2,col3) rn 
        FROM 
            xx);
    Output :
    123     x     y     100
         x1     y     200
         x     z1     300
    522     xx     yy     200
         yy     zz     100

Legend

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