This discussion is archived
10 Replies Latest reply: Jan 4, 2013 12:19 AM by BluShadow RSS

convert row to column and  delete id which repeating.

982696 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    select winner_id from merger
    union
    select loser_id from merger
  • 2. Re: convert row to column and  delete id which repeating.
    Karthick_Arp Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    if i use union i will not get ids in sequence.
  • 5. Re: convert row to column and  delete id which repeating.
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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.

Legend

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