1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help with PL/SQL, filter duplicated column values.

Discussion in 'SQL PL/SQL' started by elegantboy2014, May 11, 2014.

  1. Hi All,

    I'm a new guy in pl/sql, need your help please.
    I'm doing a report function, select data from one table named user(id, phone1, phone2, phone3)

    when querying the data, if there is duplicated phone numbers(each row), then display the first only, others blank.

    eg:

    id name phone1 phone2 phone3
    1 joe 12345 23456 12345
    2 michael 556677 556677

    the data in red are duplicated, so the result should be:

    id name phone1 phone2 phone3
    1 joe 12345 23456
    2 michael 112233


    thanks very much for your help.
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The following SQL should do what you asked. Phone1 will always be displayed as-is. If Phone2 does not match Phone1, it will be displayed. If Phone3 does not match Phone1 or Phone2, it will be displayed. It is not clear why you requested PL/SQL -- but you can certainly put this query into a cursor and use it in PL/SQL as well.

    Code (Text):
    SELECT id, name, phone1,
           DECODE(phone2, phone1, NULL, phone2) phone2,
           DECODE(phone3, phone1, NULL, phone2, NULL, phone3) phone3
    FROM   table_name