Join table with another table and fetch and replace the values which are ';' seperated-CodePudding

I am trying to join a table that has a column with data as a string and replace the values with the values from the joined table.

Tab 1

ID	Name	Categories
1	Programmer	1;2
2	Analyst	3;2

Tab 2

id	Firstname	lastname
1	john	kele
2	ajay	kashid
3	shubham	sharma

I need a query that will fetch the "Id,name and categories" from the first table but in the form like:

Id	Name	Categories
1	Programmer	john,kele ajay,kashid
2	Analyst	shubham,sharma ajay,kashid

I have written this one but this gives only the first entry, not for all the entries

SELECT 
   sc.Id,sc.Application,u.u_LastName   ', '   u.u_FirstName 'coeowner '
FROM
    Supportcentral AS sc 
outer apply [dbo].[FN_split](sc.CoeOwner, ';',0) s
 left join udcenter.dbo.[Users] u   on u.u_Login COLLATE DATABASE_DEFAULT in (select  s.item COLLATE DATABASE_DEFAULT)

CodePudding user response：

Try this Query,

SELECT Id, Name, (SELECT STRING_AGG(Name,',') FROM #Users WHERE Id IN (SELECT Value  FROM string_split(SC.Categories,';')))  AS Categories
FROM #Supportcentral    SC

CodePudding user response：

For SQL Server 17 , you may try the following:

SELECT T.ID, T.Name, 
       STRING_AGG(CONCAT(D.Firstname,' ',D.lastname),',') Categories 
FROM 
tab1 T JOIN tab2 D
ON D.ID IN (SELECT value FROM STRING_SPLIT(T.Categories, ';'))
GROUP BY T.ID, T.Name
ORDER BY T.ID, T.Name

See a demo.

I think that you misplaced the comma in your posted output, if you want exactly the posted output use this STRING_AGG(CONCAT(D.Firstname,',',D.lastname),' ').