Assistance with multiple columns accessing same table to get values

  • Here is what I'm trying to do.

    I have one table where the person's name could appear in many columns. Instead of having the name we have a userid in there. I can't figure out I create a sample of what I'm trying to do. Every time I try I just confuse myself more. So thought I'd see if I could get help here. Trying to show the display name from the user table instead of the number. Thanks for taking your time to help me.

    Table 1

    Project Main_sales_Rep, Assoc1_SalesRep, Assoc2_SalesRep, Assoc3_SalesRep, Spend

    Alpha_1, 100001, 100003, 100025, , $40,000

    Telcom_1, 100003, 100015, , , $30,000

    Telcom_2, 100001, 100003, 100015, 100025, $17,000

    User Table

    UserID, FirstName, LastName, DisplayName

    100001, Bugs, Bunny, Bugs Bunny

    100003, Porky, Pig, Porky Pig

    100015, Daffy, Duck, Daffy Duck

    100025, Petunia, Pig, Petunia Pig

    Output to be (showing the DisplayName):

    Project, Main_sales_Rep, Assoc1_SalesRep, Assoc2_SalesRep, Assoc3_SalesRep, Spend

    Alpha1, Bugs Bunny, Porky Pig, Petunia Pig, , $40,000

    Telcom1, Porky Pig, Daffy Duck, , , $30,000

    Telcom2, Bugs Bunny, Porky Pig, Daffy Duck, Petunia Pig, $17,000

  • UNPIVOT the data to get one row per project and rep, join it to the User table to assign the display name and either use PIVOT or the CrossTab method.

    If you'd like to see the coded version please provide table def and sample data in a ready to use format as described in te first link in my signature.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

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