Post reply

is this a good view?!

  • October 10, 2007 at 2:32 am

    #178828

    Hello

    I have a table (contacts) with this format:

    clientType, clientID , contactusage, contacttype, address

    internal , 1 , private , email , xxx@xxx.com

    internal , 1 , private , phone , xxx

    internal , 1 , professional , email , yyy@yyy.com

    internal , 1 , professional , phone , yyy

    I need to have a view that groups all the info of a client in one record:

    clientID, privatemail , profemail , privatephone , profphone

    1 , xxx@xxx.com , yyy@yyy.com , xxx , yyy

    This is what i did, but am concerned about performance:

    Select clientID,

    max(CASE WHEN (contactusage= 'private') AND (contacttype= 'email') THEN address ELSE NULL END) AS privatemail,

    max(CASE WHEN (contactusage= 'professional') AND (contacttype= 'email') THEN address ELSE NULL END) AS profemail,

    max(CASE WHEN (contactusage= 'private') AND (contacttype= 'phone') THEN address ELSE NULL END) AS privatephone,

    max(CASE WHEN (contactusage= 'professional') AND (contacttype= 'phone') THEN address ELSE NULL END) AS profphone

    from contacts

    where clientType = 'internal'

    group by clientID

    any comment to optimize or even re-write?!

    tx!!

  • October 10, 2007 at 3:27 am

    #740676

    Your solution looks fine to me :).

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • October 10, 2007 at 3:38 am

    #740678

    If Andras Belokosztolszki from Red Gate says it's ok, then am ok!

    Tx!!:cool:

  • October 10, 2007 at 6:41 am

    #740748

    The code you have is the classic code necessary to put records together from multiple rows in a name/value table. It's actually pretty darned fast... only thing faster would be proper table design 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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