Q: SQL CTE compare rows in the same table I have a table with customers from different data sources. There are SSN, License#, and some unique IDs but not all sources have the same IDs. I would like to compare the records on the ID columns (SSN, License, SystemID) and assign a mapped ID if same person found.
I am assuming I can use CTE but not sure where to start. Still trying to learn my way in SQL. Any help will be appreciated. Thanks.
This is how the table looks:
Source|RowID|SSN |License|SystemID
A     |1    |SSN1|Lic111 |
A     |2    |    |       |Sys666
B     |3    |SSN2|       |Sys777
C     |4    |SSN1|       |
D     |5    |    |Lic333 |
D     |6    |    |Lic333 |Sys666
E     |7    |    |       |Sys777

Results (added MapCustomerID)
Source|RowID|SSN |License|SystemID|MapCustomerID
A     |1    |SSN1|Lic111 |        |1
A     |2    |    |       |Sys666  |2
B     |3    |SSN2|       |Sys777  |3
C     |4    |SSN1|       |        |1
D     |5    |    |Lic999 |        |4
D     |6    |    |Lic333 |Sys666  |2
E     |7    |    |       |Sys777  |3


A: Here is what may be a "good-enough" approach to the problem.
Along each of the three dimensions, find the minimum row id for that dimensions (with a special handling of NULLs).  The overall customer identifier is then the minimum of these three ids.  To make it sequential with no gaps, use dense_rank().
with ids as (
      select t.*,
             (case when SSN is not null
                   then min(RowId) over (partition by SSN)
              end) as SSN_id,
             (case when License is not null
                   then min(RowId) over (partition by License)
              end) as License_id,
             (case when SystemId is not null
                   then min(RowId) over (partition by SystemId)
              end)as SystemId_id
      from t
     ),
     leastid as (
      select ids.*,
             (case when SSN_Id <= coalesce(License_Id, SSN_Id) and
                        SSN_Id <= coalesce(SystemId_id, SSN_Id)
                   then SSN_Id
                   when License_Id <= coalesce(SystemId_id, License_Id)
                   then License_Id
                   else SystemId_id
              end) as LeastId
      from ids
     )
select Source, RowID, SSN, License, SystemID,
       dense_rank(LeastId) over (order by LeastId) as MapCustomerId
from LeastIds;

This is not a complete solution, but it works for your data.  It does not work in the following case:
A     |1    |SSN1|Lic111 |        |1
A     |2    |SSN1|       |Sys666  |2
A     |3    |    |       |Sys666  |2

Because this requires two "hops".
When I have faced this situation in the past, I have created the extra column in the table and repeatedly used update to get the minimum id over the different dimensions.  Such iteration quickly connects the different pieces.  It is probably possible to write a recursive CTE to do the same thing.  But, the simpler solution above may solve your problem.
EDIT:
Because I've faced this problem before, I wanted to come up with a single query solution (rather than iterating through updates).  This is possible using recursive CTEs.  Here is code that seems to work:
with t as (
    select 'A' as source, 1 as RowId, 'SSN1' as SSN, 'Lic111' as License, 'ABC' as SystemId union all
    select 'A', 2, 'SSN1', NULL, 'Sys666' union all
    select 'A', 3, NULL, NULL, 'Sys666' union all
    select 'A', 4, NULL, 'Lic222', 'Sys666' union all
    select 'A', 5, NULL, 'Lic222', NULL union all
    select 'A', 6, NULL, 'Lic444', NULL
   ),
    first as (
      select t.*,
             (select min(RowId)
              from t t2
              where t2.SSN = t.SSN or
                    t2.License = t.License or
                    t2.SystemId = t.SystemId
             ) as minrowid
      from t
   ),
   cte as (
    select rowid, minrowid
    from first
    union all
    select cte.rowid, first.minrowid
    from cte join
         first
         on cte.minrowid = first.rowid and
            cte.minrowid > first.minrowid
    ),
    lookup as (
      select rowid, min(minrowid) as minrowid,
             dense_rank() over (order by min(minrowid)) as MapCustomerId
      from cte
      group by rowid
    )

select t.*, lookup.MapCustomerId
from t join
     lookup
     on t.rowid = lookup.rowid;

