June 17, 2011 at 5:13 pm
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
June 18, 2011 at 6:09 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply