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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy