CODEWITHSUNDEEP
sqloracle-database
user3175871
user3175871

Reputation: 13

How do I display non duplicate values in a table in Oracle?

I have a table with 9 columns under user HR in Oracle. All the columns are of number type. I need to display non duplicate values while leaving out duplicated one but I am somehow stuck in trying to reach my desired output.

CREATE TABLE HR.CUSTOMERS
(
  ID  NUMBER(4),
  PHONE1       NUMBER(8),
  PHONE2       NUMBER(8),
  PHONE3       NUMBER(8),
  PHONE4       NUMBER(8),
  PHONE5       NUMBER(8),
  PHONE6       NUMBER(8),
  PHONE7       NUMBER(8),
  PHONE8       NUMBER(8)
)

ID    PHONE1   PHONE2   PHONE3   PHONE4   PHONE5   PHONE6   PHONE7   PHONE8
1000  12345678 23456781 34567812 45678123 56781234 67812345 78123456 81234567
2000  11111111 11111111 33333333 44444444 55555555 66666666 77777777 88888888
3000  11111111 11111111 11111111 22222222          22222222

Example: ID 1000 contains no duplicate.... ID 2000 contains 2 "11111111" duplicates.... ID 3000 contains 3 "11111111" duplicates and 2 "22222222" duplicates....

The desired output is as follows:

ID    PHONE1   PHONE2   PHONE3   PHONE4   PHONE5   PHONE6   PHONE7   PHONE8
1000  12345678 23456781 34567812 45678123 56781234 67812345 78123456 81234567
2000  11111111          33333333 44444444 55555555 66666666 77777777 88888888
3000  11111111                   22222222

With the help I search from here, I can get the number of duplicate counts. However, I'm stuck in trying to move on from here to the desired output.

SELECT id,testField,
                COUNT(testField) AS fieldCount
           FROM (
SELECT id, phone1 AS testField
FROM customers
UNION ALL
SELECT id, phone2 AS testField
FROM customers
UNION ALL
SELECT id, phone3 AS testField
FROM customers
UNION ALL
SELECT id, phone4 AS testField
FROM customers
UNION ALL
SELECT id, phone5 AS testField
FROM customers
UNION ALL
SELECT id, phone6 AS testField
FROM customers
UNION ALL
SELECT id, phone7 AS testField
FROM customers
UNION ALL
SELECT id, phone8 AS testField
FROM customers
) 
GROUP BY id,testField
ORDER BY id;

The results from my try is here. FIELDCOUNT More than 1 means that there are duplicates.

ID      TESTFIELD   FIELDCOUNT
1000    12345678    1
1000    23456781    1
1000    34567812    1
1000    45678123    1
1000    56781234    1
1000    67812345    1
1000    78123456    1
1000    81234567    1
2000    11111111    2
2000    33333333    1
2000    44444444    1
2000    55555555    1
2000    66666666    1
2000    77777777    1
2000    88888888    1
3000    11111111    3
3000    22222222    2
3000        0

Upvotes: 1

Views: 194

Answers (2)

user3278460
user3278460

Reputation:

if it is ok for you to get list of phone numbers as one line you could try something like this

select id, listagg(phone, '; ') within group (order by id) phone_list
from (
select distinct id, phone
from customers
unpivot include nulls (phone for phonetype in  (PHONE1, PHONE2, PHONE3, PHONE4, PHONE5, PHONE6, PHONE7, PHONE8))
) group by id

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55554

I'm afraid the best you can do with this data model is this:

SELECT
  id,
  phone1,
  CASE WHEN phone2 NOT IN ( phone1 ) THEN phone2 END AS phone2,
  CASE WHEN phone3 NOT IN ( phone1, phone2 ) THEN phone3 END AS phone3,
  CASE WHEN phone4 NOT IN ( phone1, phone2, phone3 ) THEN phone4 END AS phone4
  -- continue
FROM customers;

Example on SQL Fiddle returns:

|   ID |   PHONE1 |   PHONE2 |   PHONE3 |   PHONE4 |
|------|----------|----------|----------|----------|
| 1000 | 12345678 | 23456781 | 34567812 | 45678123 |
| 2000 | 11111111 |   (null) | 33333333 | 44444444 |
| 3000 | 11111111 |   (null) |   (null) | 22222222 |

If that is an option, try to normalize your model into something like this:

id    phone_number  phone_number_index (if necessary)
1000  12345678      1
1000  23456781      2
2000  11111111      1

Also, a phone-"number" would usually not be stored as NUMBER but as a VARCHAR2, since the probability of having to store characters like + or / or spaces is much higher than the chance of raising all phone-numbers by 10% or other calculations.

Upvotes: 2

Related Questions