Matching another table value with comma separator

Question

Hi,

create table #temp1 (ID INT,Tags varchar(100))
insert into #temp1 (ID,tags) VALUES (101,'AAA'),(102,'BBB'),(103,'CCC')

create table #temp (Level INT,Condition varchar(100),TName VARCHAR(200))

INSERT INTO #temp (Level,Condition,TName)
VALUES (1,'L1','AAA'),(2,'L2','BBB,CCC'),(3,'[L3] OR [L3_1]','AAA,BBB,CCC')

select * from #temp
drop table #temp,#temp1

I want exact result like below

Level Condition TName
1 L1 101
2 L2 102,103
3 [L3] OR [L3_1] 101,102,103

Answer 1

select 
    t.[Level],
    t.[Condition], 
    STRING_AGG(t1.ID,',') WITHIN GROUP (ORDER BY t1.ID) AS TName
from #temp t
    CROSS APPLY string_split(t.TName,',') s
    INNER JOIN #temp1 t1
    ON t1.tags = s.[value]
GROUP BY t.[Level], t.[Condition]
ORDER BY t.[Level], t.[Condition]

Answer 2

Two solutions depending on what you mean by "exact result".

If you don't care about the order of the tags value, for example, if is it OK if your result reversed the tags on Level 2 so it looked like

2 L2 103,102

If that's OK, then you can do

Select t.Level, t.Condition, String_Agg(t1.ID, ',')
From #temp t
Cross Apply String_Split(t.TName, ',') s
Inner Join #temp1 t1 On s.value = t1.Tags
Group By t.Level, t.Condition
Order By Level;

But if the strings must always show the tags in the order from TNAME, then you will need to create a split function which preserves the order of the items. The builtin function String_Split used in the above doesn't ALWAYS do that. So then you solution would look like, first create the user function

CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
RETURNS table
/* Use Option(MaxRecursion 0) in queries that call this function if 
   there can be more than 99 delimited values in @DelimitedString */
AS
RETURN (
    WITH Pieces (ID, start, stop) AS (
      SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
      UNION ALL
      SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT ID,
      SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
    FROM Pieces
  )
GO

Then you can do

Select t.Level, t.Condition, String_Agg(t1.ID, ',') Within Group (Order By s.ID)
From #temp t
Cross Apply dbo.Split(TName, ',') s
Inner Join #temp1 t1 On s.Element = t1.Tags
Group By t.Level, t.Condition
Order By Level;

Tom

Answer 3

Hi @Mohammad Farook ,

Please also refer below:

SELECT Level,Condition,STRING_AGG( t1.id,',') TName  
FROM #temp1 AS t1  
INNER JOIN #temp AS t2  
ON ',' + t2.TName + ',' LIKE '%,' + CONVERT(VARCHAR(250),t1.Tags) + ',%'  
group by Level,Condition  
order by Level  

Output:

Level	Condition	TName  
1	L1	101  
2	L2	102,103  
3	[L3] OR [L3_1]	101,102,103  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.