How to join 2 tables where one table has multiple values in single column

Question

Hi All,

Can someone please help me on how to achieve this, below is my DDL and input code

Create Table #Temp
(
ID Int,
Code nvarchar(255)
)

Insert Into #Temp Values ( 1234,'123,456')
Insert Into #Temp Values ( 5678,NULL)
Insert Into #Temp Values ( 9012,'456')
Insert Into #Temp Values ( 8956,'123,789')

Select * From #Temp

Create table #Temp1
(
Code nvarchar(255),
codetext nvarchar(255)
)

Insert Into #Temp1 Values ( '123','Hi')
Insert Into #Temp1 Values ( '456','Hello')
Insert Into #Temp1 Values ( '789','How are you')

select * from #Temp1

Drop Table #Temp
Drop Table #Temp1

The first image will give us the input and the second image is what i am looking for in the output, Can someone please help me on how can i join to get the output

Answer 1

Here is a solution for SQL 2016:

; WITH CTE AS (
   SELECT t.ID, t.Code, t1.codetext.value('.', 'nvarchar(MAX)') AS codetext
   FROM   #Temp t
   OUTER APPLY (SELECT codetext + ','
                FROM   string_split(t.Code, ',') AS s
                JOIN   #Temp1 t1 ON s.value = t1.Code
                FOR XML PATH(''), TYPE) AS t1(codetext)
)
SELECT ID, Code, substring(codetext, 1, len(codetext) - 1)
FROM  CTE

Answer 2

Hi @sujith kumar matharasi ，

Please also check another method：

;WITH cte   
AS(SELECT t.ID,t.Code,v.value  
FROM #Temp  t  
OUTER APPLY STRING_SPLIT(t.code, ',')v)  
,cte2 AS  
(SELECT c.ID,c.Code,c.value,t1.Code Code1,t1.codetext FROM cte c  
LEFT JOIN #Temp1 t1  
ON c.value=t1.Code)  
  
SELECT ID, Code, STUFF((SELECT ',' + CAST(codetext AS VARCHAR(30)) AS [text()]  
FROM cte2 AS O  
WHERE O.ID = C.ID   
FOR XML PATH('')), 1, 1, NULL) AS codetext  
FROM cte2  AS C  
GROUP BY ID, code  

sql server 2005 and later versions can use stuff 、for xml (STUFF (Transact-SQL)
、FOR XML (SQL Server)).
sql server 2016 introduced the string_split function(STRING_SPLIT (Transact-SQL)).
sql server 2017 introduced the string_agg function(STRING_AGG (Transact-SQL)).
Hope this can help you.

Regards
Echo

---

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

Answer 3

Let me first note that storing comma-separated values in a relational database goes a cross a fundamental design point: a cell should hold a single value. If you store comma-separated values you will face lots of troubles, both with performance and klunky queries.

Here is a query that requires SQL 2017 (always include which version of SQL Serer you are using):

SELECT t.ID, t.Code, string_agg(t1.codetext, ',')
FROM   #Temp t
OUTER  APPLY string_split(t.Code, ',') AS s
LEFT   JOIN  #Temp1 t1 ON s.value = t1.Code
GROUP  BY t.ID, t.Code

Answer 4

If you believe that STRING_SPLIT does not guarantee the order of extracted substrings (according to Documentation), and also SELECT without ORDER BY gives substrings in unspecified order, but the order of output words is important in your case, then consider any alternative custom splitting function that preserves the order (can be found in these forums as well).

In addition, check if the next approach works on your server:

;
with Q as
(
    select t.ID, p, t1.codetext 
    from #Temp as t
    cross apply (values (',' + Code + ',')) as A(c)
    cross join #Temp1 as t1
    cross apply (values (charindex(',' + t1.Code + ',', A.c))) as B(p)
    where p > 0
)
select *, 
    stuff( ( select ',' + codetext from Q where Q.ID = t.ID order by Q.p for xml path('') ), 1, 1, '') as codetext 
from #Temp as t
order by ID

(It assumes that #Temp.Code does not contain repetitions).

Answer 5

Thanks to both of you , both the code worked.