How to Join Comma-Delimited Data Columns in SQL?

Joining a Comma-Delimited Data Column

Challenge:

Given two tables, one with a comma-separated data column, the task is to retrieve data from the tables and present the results with the comma-separated values being joined into a single string for each row.

Solution:

Normalization:

An ideal approach would be to normalize the table with the comma-separated data column, eliminating the need for complex string manipulations. By creating a new table where each comma-separated value becomes a new row, you can then easily join the tables on a common field.

Split Function:

If normalization is not possible, a split function can be created to convert the comma-separated values into rows that can be joined on. This function can then be used to either leave the data in multiple rows or concatenate the values back into a comma-separated list.

FOR XML PATH and STUFF:

Alternatively, you can use the FOR XML PATH and STUFF techniques to retrieve the data. FOR XML PATH will convert the comma-separated values into XML, and STUFF can be used to concatenate the XML into a single string.

Direct FOR XML PATH Application:

A final approach is to apply FOR XML PATH directly to the data column. By selecting the desired columns and using the appropriate query, you can retrieve the comma-separated values and aggregate them into a single string.

Implementation Details:

Depending on the approach chosen, the implementation will vary. The following examples provide a brief overview of how each method can be implemented:

Normalization:

CREATE TABLE NormalizedTable (
  id INT,
  value VARCHAR(255),
);

INSERT INTO NormalizedTable (id, value) VALUES
  (1, 'john'),
  (2, 'alex'),
  (3, 'piers'),
  (4, 'sara'),
  (5, 'C1'),
  (6, 'C2'),
  (7, 'C3'),
  (8, 'C4'),
  (9, 'R1'),
  (10, 'R2'),
  (11, 'R3');

SELECT DISTINCT T2.col1,
  STUFF((
    SELECT ', ' + T1.col2
    FROM NormalizedTable T1
    INNER JOIN Table2 T2 ON T1.id = T2.col2
    FOR XML PATH('')
  ), 1, 2, '');

Split Function:

CREATE FUNCTION dbo.Split
  (@String VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS TABLE
AS RETURN
  WITH CTE AS (
    SELECT 1 AS RowNum, @String AS Value
    UNION ALL
    SELECT RowNum + 1, SUBSTRING(@String, RowNum + 1, LEN(@Delimiter))
    FROM CTE
    WHERE SUBSTRING(@String, RowNum + 1, LEN(@Delimiter))  ''
  )
  SELECT
    RowNum,
    Value AS Item
  FROM CTE;

-- Usage
SELECT DISTINCT T2.col1,
  STUFF((
    SELECT ', ' + T1.col2
    FROM (
      SELECT DISTINCT Value AS col2
      FROM dbo.Split(T2.col2, ',')
    ) T1
    FOR XML PATH('')
  ), 1, 2, '');

FOR XML PATH and STUFF:

SELECT T2.col1,
  (
    SELECT ', ' + T1.col2
    FROM Table1 T1
    WHERE ',' + T2.col2 + ',' LIKE '%,' + CAST(T1.col1 AS VARCHAR(10)) + ',%'
    FOR XML PATH('')
  ).value('substring(text()[1], 3)', 'VARCHAR(MAX)') AS col2
FROM Table2 T2;

By selecting the appropriate approach and implementing the necessary code, you can effectively join comma-separated data into a single string for each row in the table.

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