SQL Join using aggregated string

Question

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

AGGREGATE_KEY	Value_1
A,B	FOO

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

KEY	Value_2
A	DAH

To achieve the following output:

AGGREGATE_KEY	Value_1	Value_2
A,B	FOO	DAH

You go and find the person that decided to put a comma delimited list of values in a single column and make them fix that nonsense. If they refuse, then threaten them with whatever power you have. If that doesn't work then you can split your delimited string into individual records with which you can do your join using STRING_SPLIT() but the performance isn't going to be amazing. If you are on an older version (pre 2016) then it's going to get ugly. — JNevill
– JNevill, Commented Oct 26, 2021 at 19:32
This is clearly a less than ideal scenario where I have to find a way to fit a square peg into a round hole. Admittedly you should never, ever put a comma delimited list of values into a single column, and have that column function as a key to another table. — Big_Red
– Big_Red, Commented Oct 26, 2021 at 21:32

Dale K · Accepted Answer · 2021-10-26 20:37:28Z

2

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     |
+---------------+---------+---------+

edited Oct 26, 2021 at 20:37

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Oct 26, 2021 at 20:21

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2021-10-26 20:37:42Z

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

Collectives™ on Stack Overflow

SQL Join using aggregated string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related