10 Replies Latest reply: Jan 4, 2013 2:19 AM by BluShadow RSS

    convert row to column and  delete id which repeating.

    982696
      In target table i should load winner_id first and then respective looser_id's of all winner_id.

      input
      table name:merger
      sno winner_id looser_id
      1 100 200
      2 304 500
      3 100 400
      4 456 700
      5 100 987

      my output should be as below
      100
      200
      400
      987
      304
      456
        • 1. Re: convert row to column and  delete id which repeating.
          908002
          select winner_id from merger
          union
          select loser_id from merger
          • 2. Re: convert row to column and  delete id which repeating.
            Karthick_Arp
            979693 wrote:
            In target table i should load winner_id first and then respective looser_id's of all winner_id.
            input
            table name:merger
            sno  winner_id   looser_id
            1       100            200
            2       304            500
            3       100            400
            4       456            700
            5       100           987
            
            my output should be as below
            100
            200
            400
            987
            304
            456
            Why 500 and 700 in looser_id is not shown in output?
            • 3. Re: convert row to column and  delete id which repeating.
              982696
              sorry i missed that two numbers..

              please check the correct output below.

              100
              200
              400
              987
              304
              500
              456
              700
              • 4. Re: convert row to column and  delete id which repeating.
                982696
                if i use union i will not get ids in sequence.
                • 5. Re: convert row to column and  delete id which repeating.
                  BluShadow
                  979693 wrote:
                  if i use union i will not get ids in sequence.
                  The example output you've shown is not in sequence either, so what do you want exactly?
                  • 6. Re: convert row to column and  delete id which repeating.
                    BluShadow
                    Is this what you're after?
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  with t as (select 1 as sno, 100 as winner_id, 200 as loser_id from dual union all
                      2             select 2, 304, 500 from dual union all
                      3             select 3, 100, 400 from dual union all
                      4             select 4, 456, 700 from dual union all
                      5             select 5, 100, 987 from dual)
                      6  --
                      7  -- end of test data
                      8  --
                      9  select id
                     10  from (
                     11        select id
                     12              ,count(*) over (partition by id order by winner_id, sno, rn) as cnt
                     13              ,row_number() over (order by winner_id, sno, rn) as rn
                     14        from (
                     15              select sno, rn, winner_id, decode(rn,1,winner_id,loser_id) as id
                     16              from   t cross join (select rownum rn from dual connect by rownum <= 2)
                     17             )
                     18       )
                     19  where cnt = 1
                     20* order by rn
                    SQL> /
                    
                            ID
                    ----------
                           100
                           200
                           400
                           987
                           304
                           500
                           456
                           700
                    
                    8 rows selected.
                    • 7. Re: convert row to column and  delete id which repeating.
                      Solomon Yakobson
                      979693 wrote:
                      if i use union i will not get ids in sequence.
                      So you want all distinct winners first followed by all distinct loosers. I'll assume if same id is both winner & looser it will appear once as a winner. If so, use:
                      with  t as (
                                   select  winner_id id,
                                           1 weight
                                     from  merger
                                  union all
                                   select  looser_id,
                                           2 weight
                                     from  merger
                                )
                      select  id
                        from  t
                        group by id
                        order by min(weight),
                                 id
                      /
                      For example:
                      SQL> with merger as (
                        2                  select 1 sno,100 winner_id,200 looser_id from dual union all
                        3                  select 2,304,500 from dual union all
                        4                  select 3,100,400 from dual union all
                        5                  select 4,456,700 from dual union all
                        6                  select 5,100,987 from dual
                        7                 ),
                        8            t as (
                        9                   select  winner_id id,
                       10                           1 weight
                       11                     from  merger
                       12                  union all
                       13                   select  looser_id,
                       14                           2 weight
                       15                     from  merger
                       16                 )
                       17  select  id
                       18    from  t
                       19    group by id
                       20    order by min(weight),
                       21             id
                       22  /
                      
                              ID
                      ----------
                             100
                             304
                             456
                             200
                             400
                             500
                             700
                             987
                      
                      8 rows selected.
                      
                      SQL>  
                      SY.
                      • 8. Re: convert row to column and  delete id which repeating.
                        982696
                        below query will sort distinct winner_id and then looser_id but my requirement is first row winner_id followed looser_id which is mapped with winner_id.
                        • 9. Re: convert row to column and  delete id which repeating.
                          jeneesh
                          979693 wrote:
                          below query will sort distinct winner_id and then looser_id but my requirement is first row winner_id followed looser_id which is mapped with winner_id.
                          SQL> with merger as
                            2  (
                            3    select 1 sno,100 winner_id,200 looser_id
                            4    from dual union all
                            5    select 2,304,500 from dual union all
                            6    select 3,100,400 from dual union all
                            7    select 4,456,700 from dual union all
                            8    select 5,100,987 from dual
                            9   ),
                           10  t as
                           11  (
                           12    select  winner_id id,
                           13            1 weight,winner_id dummy
                           14    from  merger
                           15    union all
                           16    select  looser_id,
                           17            2 weight,winner_id
                           18    from  merger
                           19  )
                           20  select  id
                           21  from  t
                           22  group by id
                           23  order by max(dummy),min(weight),id;
                          
                                  ID
                          ----------
                                 100
                                 200
                                 400
                                 987
                                 304
                                 500
                                 456
                                 700
                          
                          8 rows selected.
                          • 10. Re: convert row to column and  delete id which repeating.
                            BluShadow
                            979693 wrote:
                            below query will sort distinct winner_id and then looser_id but my requirement is first row winner_id followed looser_id which is mapped with winner_id.
                            Which is what my query was doing wasn't it?

                            I have to say, you've very poor at explaining the actual logic required.