question

MohammadFarook-3243 avatar image
0 Votes"
MohammadFarook-3243 asked MelissaMa-msft answered

Matching another table value with comma separator

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
 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]
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MohammadFarook-3243,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.