How to Efficiently Join Data Across Comma-Delimited Columns in SQL?

Joining Comma-Delimited Data Column

Comma-separated values (CSV) are commonly used to store multiple values within a single column in a relational database. However, this format can present challenges when performing data manipulation tasks. This article explores techniques for joining data across comma-delimited columns.

Example Scenario

Consider the following two tables:

Table 1 (T1)

col1	col2
C1	john
C2	alex
C3	piers
C4	sara

Table 2 (T2)

col1	col2
R1	C1,C2,C4
R2	C3,C4
R3	C1,C4

Desired Output:

col1	col2
R1	john,alex,sara
R2	piers,sara
R3	john,sara

Normalization for Optimal Performance

Ideally, the data should be normalized, eliminating comma-separated values from Table 2. By creating a new table with the following structure:

CREATE TABLE T2 (
    col1 varchar(2),
    col2 varchar(2),
    PRIMARY KEY (col1, col2),
    FOREIGN KEY (col2) REFERENCES T1 (col1)
);

Data can be inserted accordingly, enabling efficient joins:

INSERT INTO T2 (col1, col2) VALUES ('R1', 'C1'), ('R1', 'C2'), ('R1', 'C4'), ('R2', 'C3'), ('R2', 'C4'), ('R3', 'C1'), ('R3', 'C4');

Direct Query Joining:

Using the normalized tables, a simple join can retrieve the desired data:

SELECT t2.col1, t1.col2
FROM t2
INNER JOIN t1
ON t2.col2 = t1.col1;

Concatenation for Comma-Separated Output:

If the desired output requires comma-separated values, FOR XML PATH and STUFF functions can be employed:

SELECT DISTINCT t2.col1, 
STUFF(
    (SELECT DISTINCT ', ' + t1.col2
    FROM t1
    INNER JOIN t2 t ON t1.col1 = t.col2
    WHERE t2.col1 = t.col1
    FOR XML PATH ('')), 1, 1, '') AS col2
FROM t2;

Split Function for Unnormalized Data:

In the absence of normalized data, a split function can be created to divide comma-separated values into individual rows:

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
RETURNS @temptable TABLE (items varchar(MAX))       
AS       
BEGIN      
    DECLARE @idx int       
    DECLARE @slice varchar(8000)       

    SELECT @idx = 1       
    IF len(@String)<1 OR @String IS NULL RETURN       

    WHILE @idx != 0       
    BEGIN       
        SET @idx = CHARINDEX(@Delimiter, @String)       
        IF @idx != 0       
            SET @slice = LEFT(@String, @idx - 1)       
        ELSE       
            SET @slice = @String       

        IF(LEN(@slice) > 0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       

        SET @String = RIGHT(@String, LEN(@String) - @idx)       
        IF LEN(@String) = 0 BREAK       
    END   
RETURN 
END;

Using the split function within a CTE (Common Table Expression), the data can be processed:

WITH CTE AS
(
  SELECT c.col1, t1.col2
  FROM t1
  INNER JOIN 
  (
    SELECT t2.col1, i.items AS 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, '') AS col2
FROM CTE c

Alternative FOR XML PATH Query:

Another approach involves direct application of FOR XML PATH:

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;

Conclusion

Joining across comma-delimited data requires careful consideration of performance and output formatting. Normalization offers optimal performance, but if not feasible, split functions or direct FOR XML PATH queries provide alternatives. These techniques enable efficient data manipulation and retrieval from comma-separated columns.

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