supercool djkazu supercool djkazu - 6 months ago 25
SQL Question

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 jsmith@yahoo.com
El Alex EL@yahoo.com
EL ALex EL@Gmail.com
EL Alex EL@hotmail.com
Jay smith Jsm@gmail.com


I would like to show like this

pref_mail_name ----em.email_address1---em.email_address2--em.email_address3
jsmith jsmith@yahoo.com
El Alex EL@yahoo.com EL@Gmail.com EL@hotmail.com
Jay smith Jsm@gmail.com


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

Answer

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