gjshah7254
8/13/2015 - 9:02 PM

SQL - Get values of multiple or joined values in a column seperated by a comma, pipe or semicolon or any other character

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