Converting / Parsing Rows to Delimited string column in SQL

A while ago I have this post which parses a delimited string column in SQL to rows little did I know that I will needing it the other way around parsing rows to delimited string.  So in case you need that solution here is how I have done it.

Lets say we have a database structure like the one below where you have company and staff tables and they have foreign key realtionship on Company ID, where there are many staff in each company.

To give you more clarity, lets say here are the data inside your tables.

And you want to show the results like such, where you list all Staff in a CSV format column.  Thanks to XML methods this is easily achievable, for more information about FOR XML T-SQL here is a good article on how it works.

Now to do that here is my query

SELECT     CompanyName,
  (SELECT     StaffName + ',' AS 'data()'
   FROM          Staff s
   WHERE      s.CompanyID = c.CompanyID FOR XML PATH('')) AS StaffCSV
FROM         Companies c

If you output the field you need to be delimited as data it will not have that XML element tags rather it will be space delimited.  Also you notice I added a comma after the StaffName that would be our delimiter for each record.  But there will be an extra comma if we do this so to clean it I have to do this as well.

WITH StaffList as
(
SELECT     CompanyName,
  (SELECT     StaffName + ',' AS 'data()'
   FROM          Staff s
   WHERE      s.CompanyID = c.CompanyID FOR XML PATH('')) AS StaffCSV
FROM         Companies c
)
SELECT CompanyName, LEFT(StaffCSV, LEN(StaffCSV)-1) as StaffCSV from StaffList

Thats it easy and simple