April 11, 2024 at 1:35 am
Greetings,
We are trying to develop a reference name for all employees as their ID is different in different applications. We have created a names table that captures these names
ID Reference Name Name1 Name2 Name3 Name4
1JohnSmith John Smith jsmith johns hockey johnnys john.smith
2Paul Adams Paul Adams padams paula null fishing123 null
We want to update values in other Tables but they might join on any one of these name. For example, a customers table might have multiple fields (Salesperson, enteredby). Yes, a user might have different ID's in the same application. We want to set the Salesperson and EnteredBy to the ID in the Names table
Customerid Customer Name Salesperson EnteredBy
1 Ace Sales johns padams
2 City Supplies fishing123 jsmith
There are 20 columns with various name permutations and they might be null. I was trying the concat_ws function to combine all of these into one comma separated list and thought I might be able to use the string_split function to join on specific elements but can't quite get it. I'm not sure this is the best approach. At the end of the day, I want to set the user fields in other tables to the ID in the names table. Any help or suggestions would be appreciated.
April 11, 2024 at 8:27 am
If you are prepared to take the time to provide your sample data in consumable format (ie, CREATE TABLE / INSERT ... VALUES), you might get a coded answer.
If it were me, the first step I'd take in code would be to create a temp table in normalised form and populate it with your reference data
(Id, Name)
where 'Name' is any one of Name1, Name2,..., NameX.
I'd have a unique index on the Name column. Otherwise you run the risk of having the same name referencing more than one Id.
The rest is easy.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 11, 2024 at 10:15 am
Since you have not provided an expected output, hopefully this will get you started
DECLARE @RefTable table (
ID int NOT NULL
, Reference varchar(50) NULL
, Name varchar(50) NULL
, Name1 varchar(50) NULL
, Name2 varchar(50) NULL
, Name3 varchar(50) NULL
, Name4 varchar(50) NULL
, Name5 varchar(50) NULL
);
DECLARE @CustTable table (
Customerid int NOT NULL
, CustomerName varchar(50) NULL
, Salesperson varchar(50) NULL
, EnteredBy varchar(50) NULL
);
INSERT INTO @RefTable ( ID, Reference, Name, Name1, Name2, Name3, Name4, Name5 )
VALUES ( 1, 'JohnSmith', 'John Smith', 'jsmith', 'johns', 'hockey', 'johnnys', 'john.smith' )
, ( 2, 'Paul Adams', 'Paul Adams', 'padams', 'paula', null, 'fishing123', NULL );
INSERT INTO @CustTable ( Customerid, CustomerName, Salesperson, EnteredBy )
VALUES ( 1, 'Ace Sales', 'johns', 'padams' )
, ( 2, 'City Supplies', 'fishing123', 'jsmith' );
SELECT rt.*, c.*
FROM @RefTable AS rt
CROSS APPLY (VALUES (rt.Reference)
, (rt.Name)
, (rt.Name1)
, (rt.Name2)
, (rt.Name3)
, (rt.Name4)
, (rt.Name5)
) AS ref(name)
INNER JOIN @CustTable AS c ON c.Salesperson = ref.name
April 11, 2024 at 2:22 pm
The problem is that you chose to create a DENORMALIZED names table. It sounds like you're early enough to change this to NORMALIZED. That will fix your problems.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 11, 2024 at 2:33 pm
With 20 name columns, how do you verify that all the names are unique across all rows? And, if they're not unique, how do you know which id to assign for duplicate names?
A normalized table would also make it much easier to verify uniqueness (or to identify dups).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply