CODEWITHSUNDEEP
sqloracle-database
SASPYTHON
SASPYTHON

Reputation: 1621

How to query, one to many relationship, denormalize

I am sorry about the title, I am not sure how to ask. I have tables, entity and email, this is one to many relationship.

My code is

select e.pref_mail_name, em.email_address
from entity e
left join email em ON em.id_number = e.id_number

Then output is like this

pref_mail_name   --------------- em.email_address
jsmith                           [email protected]
El Alex                          [email protected]
EL ALex                          [email protected]
EL Alex                          [email protected]
Jay smith                        [email protected]

I would like to show like this

  pref_mail_name ----em.email_address1---em.email_address2--em.email_address3
  jsmith             [email protected]
  El Alex            [email protected]        [email protected]      [email protected] 
  Jay smith          [email protected]

How can I write a query to echo out like above?

Upvotes: 2

Views: 742

Answers (2)

access_granted
access_granted

Reputation: 1907

SELECT 
  e.pref_mail_name
  ,max(case when rn=1 then email_address end) email_1
  ,max(case when rn=2 then email_address end) email_2
  ,max(case when rn=3 then email_address end) email_3
FROM entity e LEFT JOIN 
  (select email_address, id_number
          ,row_number() over (partition by id_number order by 2) rn from email) em
ON (e.id_number=em.id_number)
GROUP by pref_mail_name, 2;

Upvotes: 0

morgb
morgb

Reputation: 2312

If you just want static columns, you can use the following method, which takes advantage of the lead function (you could also use lag and reverse the sorting):

select e.pref_mail_name, em.email_address1, em.email_address2, em.email_address3
from entity e
left join (
    select 
      id_number,
      rank() over (partition by id_number order by email_address asc) as email_rank,
      lead(email_address,0,null) over (partition by id_number order by id_number, email_address asc) as email_address1,
      lead(email_address,1,null) over (partition by id_number order by id_number, email_address asc) as email_address2,
      lead(email_address,2,null) over (partition by id_number order by id_number, email_address asc) as email_address3
    from email
 ) em 
    ON em.id_number = e.id_number
    AND em.email_rank = 1

Upvotes: 1

Related Questions