SQL - Get values of multiple or joined values in a column seperated by a comma, pipe or semicolon or any other character
;WITH Temp
AS
(
SELECT u.*, p.*
FROM [dbo].[CMS_User] u
INNER JOIN [dbo].ComfortGroup_Stockist p
ON
('|' + u.Store + '|' LIKE '%|' + CAST(p.StockistID AS varchar(15)) + '|%')--replace pipe with your character of seperation
)
SELECT *,
STUFF((SELECT '|' + StockistName FROM Temp WHERE UserID = t.UserID ORDER BY UserID FOR XML PATH('')),1,1,'') AS 'StockistName'--gets your field with values in it
FROM (SELECT DISTINCT UserID,Email,FirstName,LastName FROM Temp) t