Comma Separated using SQL


I've created a simple query that I have 3 or 4 records returned.

SELECT Distinct
at.Attachment_Filename AS attachment_files,
oh.Order_Number AS Order_Number
FROM dbo.Order_Detail od LEFT OUTER JOIN
dbo.Order_Header oh ON od.Order_Header_ID = oh.Order_Header_ID LEFT OUTER JOIN
dbo.Attachment at ON oh.Order_Header_ID = at.Order_Header_ID
WHERE  oh.Order_Number  =  '80237'

This returns
123456 | 80237
789012 | 80237
345678 | 80237

I'm needing results to show as
123456, 789012, 345678 | 80237

I'm running an older version of Vineyard Knowledgesync, and I believe it's using SQL 2005. So STUFF doesn't appear to work. Any help would be appreciated.

Hope this link helps you

Perhaps a more common use case is to group together and then aggregate, just like you would with SUM, COUNT or AVG.

We can use this SQL Query for example:

SELECT a.articleId, title, STRING_AGG (tag, ',') AS tags
FROM dbo.Article AS a
LEFT JOIN dbo.ArticleTag AS t
ON a.ArticleId = t.ArticleId
GROUP BY a.articleId, title;

Thanks for looking into this, but STRING_AGG is not supported in my version. Any other help would be appreciated

Give this a try:

SELECT oh.Order_Number AS Order_Number
	,attachment_files = COALESCE(STUFF((SELECT ', ' + at.Attachment_Filename
						FROM dbo.Attachment at 
						WHERE oh.Order_Header_ID = at.Order_Header_ID
						ORDER BY at.Attachment_Filename
						FOR XML PATH('')
						), 1, 2, N''
					), '') 
FROM dbo.Order_Header oh 
WHERE oh.Order_Number = '80237'

The LEFT OUTER JOIN with Order_Detail is not needed in the query.

Thank you so much for this. I had given up on this, and just noticed that you had responded to this. Thanks again!!