Remove redundant data in select statement

  • Hello,

    Having these two tables




    ID Name

    -- --------

    1 name1

    2 name2

    3 name3




    ID ContactDetailID EmailAddress

    -- ----------------- ---------------

    1 1 Address1

    2 1 Address2

    3 1 Address3

    4 2 Address4

    5 2 Address5

    6 2 Address6

    when i select all contacts with emails by the below query




    FROM ContactDetail CD

    LEFT JOIN Emails EM ON EM.ContactDetailID = CD.ID

    I got the below result

    ID Name EmailAddress

    -- -------- --------------

    1 name1 Address1

    1 name1 Address2

    1 name1 Address3

    2 name2 Address4

    2 name2 Address5

    2 name2 Address6

    3 name3 NULL

    what is the best solution to get it like below

    ID Name EmailAddress

    --- ------ ---------------

    1 name1 Address1

    NULL NULL Address2

    NULL NULL Address3

    2 name2 Address4

    NULL NULL Address5

    NULL NULL Address6

    3 name3 NULL

    Thank you in advance,



  • To be blunt. Don't.

    That's formatting for display, not data retrieval (unless Address3 has no user, which is what your example implies).

    If you want to display like that, look at the settings and properties of whatever you're using as a front end. Reporting Services for example will let you chose the names as a grouping level and the addresses as details.

    In SQL however, the rows should be independant (especially since there's no order by or anything suggesting order in your example) and so your example is equivalent to

    NULL NULL Address2

    NULL NULL Address3

    NULL NULL Address5

    NULL NULL Address6

    1 name1 Address1

    2 name2 Address4

    3 name3 NULL

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I know that I can do it from any front end but I need to send it to a user in this format when he call procedure.

    The problem is they asked me to return the reault in that way and i need the best solution for that.

    I know how to do it with a loop and other ideas but i m asking you as experts for a better performance what will be the best solution to return the result in this way.

    Any way Thank you for your reply



  • I have to agree with Gail. The grouping (presumably for a leveled report) should be done on the front end.

Viewing 4 posts - 1 through 3 (of 3 total)

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