Multiple row issue

  • faulknerwilliam2

    SSCrazy

    Points: 2299

    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.

  • John Mitchell-245523

    SSC Guru

    Points: 148519

    What do you want the output to look like when an individual has three or more addresses?

    John

  • faulknerwilliam2

    SSCrazy

    Points: 2299

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148519

    You need a dynamic crosstab, then.

    John

  • Brahmanand Shukla

    Right there with Babe

    Points: 714

    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 5 (of 5 total)

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