Hello
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
FROM
Reference_Table as RT
LEFT OUTER JOIN Email_Table as ET
ON RT.field = ET.field
WHERE ...
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 js@x.com
John Smith 1, The Street js1@y.com
My desired output would be:
Name Address Email 1 Email 2
John Smith 1, The Street js@x.com js1@y.com
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.
December 16, 2019 at 9:42 am
What do you want the output to look like when an individual has three or more addresses?
John
December 16, 2019 at 9:50 am
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 js@x.com js1@y.com anotheremail@y.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.
John
December 16, 2019 at 10:20 am
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)
You must be logged in to reply to this topic. Login to reply