I have a scenario that I need to "match" records based on multiple attributes of a person. For instance, if a FirstName and LastName match, or a NickName and LastName match, those two scenarios should be grouped into one larger match. Here's the example data in SQLFiddle: http://www.sqlfiddle.com/#!18/0ca91/7
I'm generating a match key from the record attributes. The result gives me two different match keys and three total records. I need a result that has only one match key generated, and eventually I'm going to group all three records into one golden record in a separate step. I cannot figure out a way to logically group these records together either by "group by" or by using DENSE_RANK to generate my match key. Any help would be greatly appreciated! Thanks!
CREATE TABLE Persons (
ID int,
FirstName varchar(255),
LastName varchar(255),
NickName varchar(255)
);
INSERT INTO Persons
SELECT 1 AS ID, 'NIKKI' AS FNAME, 'MADISON' AS LNAME, 'Nikki' AS NickName
UNION ALL
SELECT 2 AS ID, 'NICOLE' AS FNAME, 'MADISON' AS LNAME, 'NICOLE' AS NickName
UNION ALL
SELECT 3 AS ID, 'NICOLE' AS FNAME, 'MADISON' AS LNAME,'Nikki' AS NickName
SELECT
*
, DENSE_RANK() OVER (ORDER BY TRIM(LastName), TRIM(FirstName)) AS GroupKey
FROM Persons
Desired Result:
GroupKey
1
1
1