Multiple row issue

  • Hello

    I have a query where I join a table to another table that stores email addresses. So a query may go:

    SELECT, RT.address,


    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     

    John Smith    1, The Street     

    My desired output would be:

    Name            Address                       Email 1         Email 2

    John Smith    1, The Street     

    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     


    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)

You must be logged in to reply to this topic. Login to reply