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

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

CodePudding user response：

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.

CodePudding user response：

Unpivot and select distinct:

with
  test_data (first_client_number, first_client_email,
             second_client_number, second_client_email) as (
    select 1111, '[email protected]', 2222, '[email protected]' from dual union all
    select 3333, '[email protected]', 3333, '[email protected]' from dual union all
    select 4444, '[email protected]', 4444, '[email protected]' from dual union all
    select 6666, '[email protected]', 7777, '[email protected]' 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   [email protected]
         4444   [email protected]
         2222   [email protected]
         6666   [email protected]
         7777   [email protected]
         1111   [email protected]