3 Replies Latest reply: Jan 4, 2013 4:30 AM by ranit B RSS

    Query

    977863
      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
          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
             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
              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