October 18, 2011 at 10:52 am
I'm trying to figure out the best way to accomplish the following situation:
Table 1 has two columns (Name, SegmentId)
EX:
Name | SegmentId
John 1
Tim 1
Table 2 has two columns (SegmentId, Code)
SegmentId | Code
1 AAA
1 BBB
1 CCC
I want to join between the tables and assign a Code to each name without it repeating. So my result will be:
Name | Code
John AAA
Tim BBB
Is this possible to accomplish through a join without a loop? I appreciate your help and can answer most SSIS questions if you ever have one!
October 18, 2011 at 10:58 am
Your question is a bit vague on the details but I think you could brute force this using ROW_NUMBER. Of course if your second table has fewer rows than your first table you would have to have duplicates. I am about to head out for lunch but I could take a look at this when I get back.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2011 at 10:58 am
What is your criteria for determining which code goes to which name?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 18, 2011 at 11:09 am
Sorry for the vagueness and I appreciate your help. There is no criteria for how the Code is assigned to a name as long as it doesn't repeat.
In sequence would be most ideal. So the first name gets AAA, second name gets BBB, third name gets CCC, etc. I just don't want to end up with two names with the same Code.
October 18, 2011 at 11:33 am
This is actually quite easy. You need to order each of the tables separately and then join the two tables both on the segmentID and the order. I used Row_Number() to specify the order.
WITH Names( [Name], SegmentID ) AS (
SELECT 'John', 1
UNION ALL
SELECT 'Tom', 1
)
, Segments( SegmentID, Code ) AS (
SELECT 1, 'AAA'
UNION ALL
SELECT 1, 'BBB'
UNION ALL
SELECT 1, 'CCC'
)
, NamesRanked AS (
SELECT [Name], SegmentID
, Row_Number() OVER( PARTITION BY SegmentID ORDER BY [Name] ) AS rn
FROM [Names]
)
, SegmentsRanked AS (
SELECT SegmentID, Code
, Row_Number() OVER( PARTITION BY SegmentID ORDER BY Code ) AS rn
FROM Segments
)
SELECT n.Name, s.Code
FROM NamesRanked AS n
FULL OUTER JOIN SegmentsRanked AS s
ON n.SegmentID = s.SegmentID
AND n.rn = s.rn
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 18, 2011 at 11:53 am
Nice solution, Drew. Very smooth.
OP, if you want the extra non-matches to not show, thrown a WHERE n.Name IS NOT NULL
AND s.Code IS NOT NULL; to the end of the query.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 18, 2011 at 12:48 pm
toddasd (10/18/2011)
Nice solution, Drew. Very smooth.OP, if you want the extra non-matches to not show, thrown a
WHERE n.Name IS NOT NULLAND s.Code IS NOT NULL; to the end of the query.
Actually, you would get rid of those by changing it from a FULL OUTER JOIN to an INNER JOIN. You may also want to use either a LEFT or RIGHT OUTER JOIN instead of a FULL OUTER JOIN.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 18, 2011 at 2:10 pm
Drew,
Excellent solution. I highly appreciate your help.
Travis
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply