Need query to join table on column with comma separated

Question

Table1

ID	Notes	ReasonID
1	Test1	[11,12]
2	Test2	[13,14]

Table 2

Reasonid	Name
11	Other1
12	Other2
13	Other3
14	Other4

Result should look like this, where Notes column from Table1 should concat with Name column from Table2.

ID	Final_Notes
1	Test1,Other1,Other2
2	Test2,Other3,Other4

Answer 1

If you use SQL Server 2017+, you may try to parse the ReasonID column as JSON, use an appropriate JOIN and then aggregate with STRING_AGG().

Sample data:

SELECT *
INTO Table1
FROM (VALUES
   (1, 'Test1', '[11,12]'),
   (2, 'Test2', '[13,14]')
) t (ID, Notes, ReasonID)

SELECT *
INTO Table2
FROM (VALUES
   (11, 'Other1'),
   (12, 'Other2'),
   (13, 'Other3'),
   (14, 'Other4')
) t (ReasonID, Name)

Statement:

SELECT 
   ID,
   FinalNotes = CONCAT(
      Notes,
      ',',
      (
      SELECT STRING_AGG(t2.Name, ',') WITHIN GROUP (ORDER BY CONVERT(int, j.[key]))
      FROM OPENJSON(ReasonID) j
      -- Important, JOIN with possible implicit conversion
      JOIN Table2 t2 ON j.[value] = t2.ReasonID
      )
   )
FROM Table1

Result:

ID	FinalNotes
1	Test1,Other1,Other2
2	Test2,Other3,Other4

db<>fiddle

Answer 2

Please try the following solution.

It will work starting from SQL Server 2012 onwards.

It is using the following:

• XML/XQuery to tokenize comma separated list of values.
• FOR XML PATH to compose FinalNotes comma separated list.

SQL

-- DDL and sample data population, start
DECLARE @Table1 TABLE(ID INT, Notes VARCHAR(60), ReasonID VARCHAR(60));
INSERT INTO @Table1(ID, Notes, ReasonID) VALUES 
(1, 'Test1', '[11,12]'), 
(2, 'Test2', '[13,14]');

DECLARE @Table2 TABLE(Reasonid INT, Name VARCHAR(60));
INSERT INTO @Table2(Reasonid, Name) VALUES 
(11, 'Other1'), 
(12, 'Other2'), 
(13, 'Other3'), 
(14, 'Other4');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

;WITH rs AS
(
    SELECT ID, Notes, Name
    FROM @Table1 AS t
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(REPLACE(REPLACE(ReasonID,'[',''),']',''), @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t1(c)
    CROSS APPLY c.nodes('/root/r/text()') AS t2(x)
        INNER JOIN @Table2 AS t3 ON t3.Reasonid = x.value('.', 'INT')
)
SELECT ID, CONCAT(Notes
    , (SELECT @separator + c.Name AS [text()]
       FROM rs AS c
       WHERE c.ID = p.ID
       FOR XML PATH(''))) AS FinalNotes
FROM rs AS p
GROUP BY ID, Notes;

Output

+----+---------------------+
| ID |     FinalNotes      |
+----+---------------------+
|  1 | Test1,Other1,Other2 |
|  2 | Test2,Other3,Other4 |
+----+---------------------+

Answer 3

use SUBSTRING(string, 2, LEN(string)-2) for deleting [] and Parsename to split based on comma and join and concat as follows

Your data

DECLARE @Table1 TABLE(
  ID INTEGER NOT NULL, 
  Notes VARCHAR(60) NOT NULL, 
  ReasonID VARCHAR(60) NOT NULL
);
INSERT INTO @Table1(ID, Notes, ReasonID) 
VALUES 
  (1, 'Test1', '[11,12]'), 
  (2, 'Test2', '[13,14]');

DECLARE @Table2 TABLE(
  Reasonid INTEGER NOT NULL, 
  Name VARCHAR(60) NOT NULL
);
INSERT INTO @Table2(Reasonid, Name) 
VALUES 
  (11, 'Other1'), 
  (12, 'Other2'), 
  (13, 'Other3'), 
  (14, 'Other4');

your query

SELECT id,
       Concat(notes, ',', T2.name, ',', T3.name) FinalNotes
FROM  (SELECT id,
              notes,
              Parsename(Replace(SUBSTRING(ReasonID, 2, LEN(ReasonID)-2), ',', '.'), 2) R1,
              Parsename(Replace(SUBSTRING(ReasonID, 2, LEN(ReasonID)-2), ',', '.'), 1) R2
       FROM   @table1) T1
      join @table2 T2
        ON T1.R1 = T2.reasonid
      join @table2 T3
        ON T1.R2 = T3.reasonid  

by using XML

 DROP TABLE IF EXISTS #t -- temporary table
  select t1.ID,t1.Notes, Name into #t -- temporary table
  from 
(
SELECT A.ID,a.Notes,  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT ID, Notes, 
         CAST ('<M>' + REPLACE(SUBSTRING(ReasonID, 2, LEN(ReasonID)-2)  , ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  @Table1) AS A CROSS APPLY String.nodes ('/M') AS Split(a)) t1 
     join @Table2 t2 on t1.String=t2.Reasonid
---XML Path
SELECT  ID,concat(notes,',',
       STUFF((SELECT ', ' + CAST(name AS VARCHAR(10)) [text()]
         FROM #t t1
         WHERE t1.ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ')) FinalNotes
FROM #t t
GROUP BY ID,notes