Need query to join table on column with comma separated-CodePudding

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

CodePudding user response：

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

CodePudding user response：

use Trim('[] ' FROM 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(Trim('[] ' FROM reasonid), ',', '.'), 2) R1,
              Parsename(Replace(Trim('[] ' FROM reasonid), ',', '.'), 1) R2
       FROM   @table1) T1
      join @table2 T2
        ON T1.R1 = T2.reasonid
      join @table2 T3
        ON T1.R2 = T3.reasonid

In addition you can use stuff and for xml path in your sql version