I have a query where I join a table to another table that stores email addresses. So a query may go:
SELECT RT.name, RT.address, ET.email
Reference_Table as RT
LEFT OUTER JOIN Email_Table as ET
ON RT.field = ET.field
However more than one email address can be stored per individual, which can give me this output:
Name Address Email
John Smith 1, The Street email@example.com
John Smith 1, The Street firstname.lastname@example.org
My desired output would be:
Name Address Email 1 Email 2
John Smith 1, The Street email@example.com firstname.lastname@example.org
eg having everything on one row, not two rows.
Please can you advise the simplest way of achieving this (if it is simple)? Thank you.
What do you want the output to look like when an individual has three or more addresses?
Thanks for getting back John. Yes, I should have said: there is actually an instance where there are 17(!) email addresses for one individual.
For the purposes of what I'm trying to do then:
Name Address Email 1 Email 2 Email 3
John Smith 1, The Street email@example.com firstname.lastname@example.org email@example.com
However most records have a maximum of two emails; some have three and a very small majority >3
You need a dynamic crosstab, then.
You can go with DYNAMIC PIVOT/CROSSTAB as suggested by John. Alternatively, if you know the fix number of email id's then you can also go with the equivalent number of LEFT OUTER JOINS.
I was wondering, how can be a person having more than 3 email id's? You must leverage this opportunity to also highlight to the correct team/do it yourself (as the case may be) to correct the data.
Viewing 5 posts - 1 through 4 (of 4 total)