Matching another table value with comma separator

Mohammad Farook 161 Reputation points
2021-09-29T13:29:18.413+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,665 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,741 Reputation points
    2021-09-29T15:11:28.937+00:00
    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]
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Tom Cooper 8,471 Reputation points
    2021-09-29T15:17:24.927+00:00

    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

    0 comments No comments

  2. MelissaMa-MSFT 24,201 Reputation points
    2021-09-30T01:30:00.203+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.