Difficulty grabbing values from same table where all the columns use the same key

Question

Difficulty grabbing values from same table where all the columns use the same key

kickersdj

Valued Member

Points: 66
More actions
June 16, 2011 at 5:00 pm

#72278

Not sure if the description fits 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
ProjectMain_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

Viewing 12 posts - 1 through 11 (of 11 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

kickersdj (6/16/2011)
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.
Table 1
ProjectMain_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

Sorry but I'm not clear as to why you want the output that you specified?

BTW, your tables are not normalized.

Are you just starting this project? If so I suggest you that start by creating a design in 3rd Normal Form before you start to write queries.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

kickersdj (6/16/2011)
Not sure if the description fits 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
ProjectMain_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

Your post is absolutely clear. What would help a lot is "readily consumable data" so I could spend the time writing code to solve your problem instead of taking the time to load your data. See the first link in my signature line below.

Since you're almost brand new to this forum, I'll format the data to load it so I can give you a working example this time, but do read the link and learn how to post the data correctly for your next post. Thanks. 🙂

--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)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Is it too late to normalize Table1?

Just wondering but what is the definition of the "Spend" Column?

Which Sales Rep is the Spend Column associated with, all of the Sales Reps or an aggregate of all of the Sales Reps for a given project?

Will you need to differentiate between each Sales Reps Sales? Someone may ask you that question and if you do not store it that could be an issue. 😉

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Here's one solution. As normal for me, the details are in the comments in the code. Post back if you have any questions. I'd also like to stress Corgi's question above. Denormalized tables like the one you have will only lead to more and more pain as time wears on.

--========================================================================================

-- Build the test data to demonstrate with.

-- Nothing in this section is a part of the solution. We're just building test data.

--========================================================================================

--===== Conditionally drop the test tables to make reruns easier in SSMS

IF OBJECT_ID('tempdb..#Table1' ,'U') IS NOT NULL DROP TABLE #Table1;

IF OBJECT_ID('tempdb..#UserTable','U') IS NOT NULL DROP TABLE #UserTable;

GO

--===== Build and populate "Table 1" on the fly.

SELECT d.*

INTO #Table1

FROM (

SELECT 'Alpha_1' ,100001,100003,100025,NULL ,40000 UNION ALL

SELECT 'Telcom_1',100003,100015,NULL ,NULL ,30000 UNION ALL

SELECT 'Telcom_2',100001,100003,100015,100025,17000

) d (Project,Main_sales_Rep,Assoc1_SalesRep,Assoc2_SalesRep,Assoc3_SalesRep,Spend)

;

--===== Build and populate the "User Table" on the fly.

SELECT d.*

INTO #UserTable

FROM (

SELECT 100001,'Bugs' ,'Bunny','Bugs Bunny' UNION ALL

SELECT 100003,'Porky' ,'Pig' ,'Porky Pig' UNION ALL

SELECT 100015,'Daffy' ,'Duck' ,'Daffy Duck' UNION ALL

SELECT 100025,'Petunia','Pig' ,'Petunia Pig'

) d (UserID, FirstName, LastName, DisplayName)

;

--========================================================================================

-- This is one solution. It unpivots the data to make a join for names easier,

-- does the join and gets the name, and repivots the data for display. It works

-- very quickly and only "dips" each table once instead of dipping the user table

-- 4 times (once for each column).

--========================================================================================

WITH

cteUnPivot AS

( --=== Unpivot the data to make it easier to join to

-- This would also be the beginning of how to normalize the table except you'd have

-- ID's for the attributes instead of the names of the attributes to make an

-- "associative" table (goes by many other names, as well).

SELECT d.Project, d.Attribute, d.Value

FROM #Table1

CROSS APPLY

(

SELECT Project, 'Main_sales_Rep' , Main_sales_Rep UNION ALL

SELECT Project, 'Assoc1_SalesRep', Assoc1_SalesRep UNION ALL

SELECT Project, 'Assoc2_SalesRep', Assoc2_SalesRep UNION ALL

SELECT Project, 'Assoc3_SalesRep', Assoc3_SalesRep UNION ALL

SELECT Project, 'Spend' , Spend

) d (Project, Attribute, Value)

WHERE d.Value IS NOT NULL

)

,

cteMatchNames AS

( --=== Get the user names ignoring the fact that "Spend" is not a user

SELECT upvt.Project, upvt.Attribute, upvt.Value, u.DisplayName

FROM cteUnPivot upvt

LEFT JOIN #UserTable u

ON upvt.Value = u.UserID

) --=== Repivot the data for display using a high speed, classic, Cross Tab.

SELECT Project,

Main_sales_Rep = MAX(CASE WHEN Attribute = 'Main_sales_Rep' THEN DisplayName ELSE '' END),

Assoc1_SalesRep = MAX(CASE WHEN Attribute = 'Assoc1_SalesRep' THEN DisplayName ELSE '' END),

Assoc2_SalesRep = MAX(CASE WHEN Attribute = 'Assoc2_SalesRep' THEN DisplayName ELSE '' END),

Assoc3_SalesRep = MAX(CASE WHEN Attribute = 'Assoc3_SalesRep' THEN DisplayName ELSE '' END),

Spend = MAX(CASE WHEN Attribute = 'Spend' THEN Value ELSE 0 END)

FROM cteMatchNames

GROUP BY Project

ORDER BY Project

;

--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)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

Almost forgot. Please see the following link for how Cross Tabs work and how they compare to Pivot.

http://www.sqlservercentral.com/articles/T-SQL/63681/

--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)

Naked Ape Ten Centuries Points: 1095 More actions · Answer 6

All issues with normalisation aside, something like the following query should give you the result you are after for the table structures given;

SELECT sales.Project, rep1.DisplayName, rep2.DisplayName, rep3.DisplayName, rep4.DisplayName, sales.Spend

FROM [Table 1] sales

LEFT JOIN User rep1 ON spend.Main_Sales_Rep=rep1.UserID

LEFT JOIN User rep2 ON spend.Assoc1_Sales_Rep=rep2.UserID

LEFT JOIN User rep3 ON spend.Assoc2_Sales_Rep=rep3.UserID

LEFT JOIN User rep4 ON spend.Assoc3_Sales_Rep=rep4.UserID

I agree with earlier posts - not a great table design - but workable. If you can redesign (normalise) it, that would be worth considering.

Chris

Naked Ape Ten Centuries Points: 1095 More actions · Answer 7

Oops - got my aliases a bit screwed up there ... I should have checked a bit more before posting;

SELECT sales.Project, rep1.DisplayName, rep2.DisplayName, rep3.DisplayName, rep4.DisplayName, sales.Spend

FROM [Table 1] sales

LEFT JOIN User rep1 ON sales.Main_Sales_Rep=rep1.UserID

LEFT JOIN User rep2 ON sales.Assoc1_Sales_Rep=rep2.UserID

LEFT JOIN User rep3 ON sales.Assoc2_Sales_Rep=rep3.UserID

LEFT JOIN User rep4 ON sales.Assoc3_Sales_Rep=rep4.UserID

Chris

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

Naked Ape (6/19/2011)
Oops - got my aliases a bit screwed up there ... I should have checked a bit more before posting;
SELECT sales.Project, rep1.DisplayName, rep2.DisplayName, rep3.DisplayName, rep4.DisplayName, sales.Spend
FROM [Table 1] sales
LEFT JOIN User rep1 ON sales.Main_Sales_Rep=rep1.UserID
LEFT JOIN User rep2 ON sales.Assoc1_Sales_Rep=rep2.UserID
LEFT JOIN User rep3 ON sales.Assoc2_Sales_Rep=rep3.UserID
LEFT JOIN User rep4 ON sales.Assoc3_Sales_Rep=rep4.UserID

That code is nice and short but it does exactly what I was avoiding... it makes 4 hits on the same table.

--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)

kickersdj Valued Member Points: 66 More actions · Answer 9

kickersdj

Valued Member

Points: 66

June 20, 2011 at 9:34 am

#1341272

Jeff thanks a lot! Just what I needed.

kickersdj Valued Member Points: 66 More actions · Answer 10

And Naked Ape, thanks too! It was actually your code that got it for me.

kickersdj Valued Member Points: 66 More actions · Answer 11

Thanks Jeff, I will read your link on how to post data.