Flatten table to get unique Email Addresses

Question

I have a table that contains 4 fields. first_client_number, first_client_email, second_client_number, second_client_email. Something like:

first_client_number	first_client_email	second_client_number	second_client_email
1111	na1@na.com	2222	na2@na.com
3333	na3@na.com	3333	na3@na.com
4444	na4@na.com	4444	na4@na.com
6666	na6@na.com	7777	na7@na.com
etc

Where:

• the first client number and the second client number can be different
• the first client number and the second client number can be the same

I want to get a unique list of all emails so that I have one column that shows the client number and a second column to show the clients email address. How do I flatten the table to get my desired result?

I am querying in Oracle v.12

Thanks

Answer 1

The fields in your description don't match the fields defined as column headers on your table... in the question: I'm assuming your table column headers are correct

One method would be to use an "UNPIVOT"

Doc link: https://www.oracle.com/technical-resources/articles/database/sql-11g-pivot.html

But given the limited columns I think this is easiest; splitting the data and using a simple union

SELECT first_client_number as client_number, first_client_email as Client_email
FROM table

UNION

SELECT second_client_number, second_client_email
FROM Table

Notes:

• we can't define first or second so we have to generate an alias for the columns. We don't need to alias the 2nd query as it inherits the names from the first.
• It assumes the data types for the 1st and 2nd columns respectively are identical.
• We don't use union all as we don't want to keep duplicates and union will perform a distinct on the result set eliminating the undesired duplicates.

Answer 2

Unpivot and select distinct:

with
  test_data (first_client_number, first_client_email,
             second_client_number, second_client_email) as (
    select 1111, 'na1@na.com', 2222, 'na2@na.com' from dual union all
    select 3333, 'na3@na.com', 3333, 'na3@na.com' from dual union all
    select 4444, 'na4@na.com', 4444, 'na4@na.com' from dual union all
    select 6666, 'na6@na.com', 7777, 'na7@na.com' from dual
  )
--   End of test data (not part of the actual query); remove and replace
--   table name with the actual table name
select  distinct client_number, client_email
from    test_data
unpivot (   (client_number, client_email)
            for col in (
                         (first_client_number, first_client_email),
                         (second_client_number, second_client_email)
                       )
        )
;


CLIENT_NUMBER   CLIENT_EMAIL
-------------   ---------------------
         3333   na3@na.com
         4444   na4@na.com
         2222   na2@na.com
         6666   na6@na.com
         7777   na7@na.com
         1111   na1@na.com