Suppose I have a table in SQL Server that looks like this:

And I want to join that table to another table that looks like this

To achieve the following output:

CodePudding user response：

Expanding on my comment. If you are stuck doing this because you can't fix the data (because the schema really is broken as it is) and you are on a newer version SQL Server, you can use string_split() to split your delimited string into individual records before joining. If your data is large though, this is going to be slow.

SELECT 
  t1.aggregate_key, 
  t1.value_1,
  t2.value_2
FROM t1
   CROSS APPLY STRING_SPLIT(AGGREGATE_KEY, ',') keys
   INNER JOIN t2 ON keys.value = t2.[key];


 --------------- --------- --------- 
| aggregate_key | value_1 | value_2 |
 --------------- --------- --------- 
| A,B           | FOO     | DAH     |
 --------------- --------- --------- 

CodePudding user response：

It would be better if you normalized your table design. It is recommended and would make your queries and joins more efficient.

With the current shared example, you may use LIKE to match both fields. CONCAT may be used to add the wildcards to assist with pattern matching.

SELECT 
    t1.*, 
    t2.Value_2, 
    CONCAT(',',t1.AGGREGATE_KEY,',') as NEW_T1_AGGREGATE_KEY,
    CONCAT('%,',t2.[KEY],',%')  as NEW_T2_KEY
FROM t1
INNER JOIN t2 ON CONCAT(',',t1.AGGREGATE_KEY,',')  LIKE CONCAT('%,',t2.[KEY],',%') 
GO

AGGREGATE_KEY | Value_1 | Value_2 | NEW_T1_AGGREGATE_KEY | NEW_T2_KEY
:------------ | :------ | :------ | :------------------- | :---------
A,B           | FOO     | DAH     | ,A,B,                | %,A,%     

SELECT 
    t1.*, 
    t2.Value_2
FROM t1
INNER JOIN t2 ON CONCAT(',',t1.AGGREGATE_KEY,',')  LIKE CONCAT('%,',t2.[KEY],',%') 
GO

AGGREGATE_KEY | Value_1 | Value_2
:------------ | :------ | :------
A,B           | FOO     | DAH    

See working demo db<>fiddle here