So I am newer to oracle sql and need a data extract.

I have a table with informations about customers (customers), they can have multiple emails (emailaddresses) which can have multiple usages (usage).

At the moment I have something like this:

Select emailaddresses.email as primary, customer.uid as customerUid,
emailaddresses.email as workmail 
Join emailaddresses on emailaddresses.parentid = customer.id
Join usages on usages.parent_id = emailaddresses.id  .... -- here I am stuck

workmail: (where usage.usagetype = 'work';) and primary: (where usage.usagetype = 'primary';)

-- now the issue is, i dont know how to select both workmails and primary mails into this extract for one and the same customer. (and customer - uid and id are not the same, I did not invent it and I cannot change it. I just need an extract)

my tables and columns:

customer

• uid (int)
• id (varchar)

usages

• parent_id (int) -- links to emailaddresses.id
• customer_id (varchar) -- links to customer.id
• usagetype (varchar)

emailaddresses

• id (int)
• parentid (varchar) -- links to customer.id
• email (varchar)

My expected outcome:

CodePudding user response：

one way you could do is use LISTAGG as follows,

select customer_id, listagg(email_id || '   ,   ') within group (order by customer_id) FROM (
select 1 as customer_id, '[email protected]' as email_id , 'primary' as usagetype from dual UNION
select 1 as customer_id, '[email protected]' as email_id , 'work' as usagetype from dual UNION
select 2 as customer_id, '[email protected]' as email_id , 'work' as usagetype from dual 
)group by customer_id

CodePudding user response：

This should do:

SELECT *
  FROM (SELECT CST.UID
             , EA.EMAIL,
             , USG.USAGETYPE
          FROM EMAILADDRESSES EA
             , CUSTOMER       CST
             , USAGES         USG
         WHERE EA.PARENTID = CST.ID
           AND EA.ID       = USG.PARENT_ID 
           AND USG.USAGETYPE IN ('work','primary'))
 PIVOT (MAX(EMAIL) FOR USAGETYPE IN ('work','primary'));