How to Join Tables with Comma-Delimited Columns?

Joining comma-delimited data columns

To join two tables containing comma-delimited data columns, consider the following approaches:

Normalization

Normalize the table containing the comma-separated values by creating a separate table with a column for each value. This simplifies querying and avoids complex string manipulation.

Create a Split Function

Create a split function that converts the comma-separated string into individual rows. This allows you to perform joins on the rows.

Use FOR XML PATH and STUFF

Use FOR XML PATH to concatenate the values into a comma-separated string and STUFF to remove the leading comma.

Directly Apply FOR XML PATH

Apply FOR XML PATH directly to the data to generate the desired output.

Examples

Split Function with Concatenation

;with cte as
(
  select c.col1, t1.col2
  from t1
  inner join 
  (
    select t2.col1, i.items col2
    from t2
    cross apply dbo.split(t2.col2, ',') i
  ) c
    on t1.col1 = c.col2
) 
select distinct c.col1, 
  STUFF(
         (SELECT distinct ', ' + c1.col2
          FROM cte c1
          where c.col1 = c1.col1
          FOR XML PATH ('')), 1, 1, '') col2
from cte c

FOR XML PATH and STUFF

select col1, 
(
  select ', '+t1.col2
  from t1
  where ','+t2.col2+',' like '%,'+cast(t1.col1 as varchar(10))+',%'
  for xml path(''), type
).value('substring(text()[1], 3)', 'varchar(max)') as col2
from t2;

The above is the detailed content of How to Join Tables with Comma-Delimited Columns?. For more information, please follow other related articles on the PHP Chinese website!