Converting part of a result set to a comma separated list
One of the common task that I have come across is the need to convert a list of results into a comma separated list. There are many ways to use this, one if which is in reports.
Download the AsCSV.sql sample here.
Often times I have told developers, here is how you do it, and if you Google on it you will find some great samples. Now it is time that I provide my own sample. I call this the FOR XML hack since it used the FOR XML command, without actually involving any XML at all.
-- first query showing columns
SELECT c.firstname + ' ' + c.lastname
FROM [AdventureWorks].[HumanResources].[Employee] e
INNER JOIN [AdventureWorks].[Person].[Contact] c
ON c.contactid = e.contactid
ORDER BY c.lastname;
GO
-- second query as csv
SELECT Substring((SELECT ', ' + c.firstname + ' ' + c.lastname
FROM [AdventureWorks].[HumanResources].[Employee] e
INNER JOIN [AdventureWorks].[Person].[Contact] c
ON c.contactid = e.contactid
ORDER BY c.lastname
FOR XML PATH('')),3,10000000) AS list;
The output from the above queries will look like this.
Although that is useful, and I have on many occasions used a very similar query, where its gets interesting is to create a comma separated list as part of a result set, not as the whole result set.
For example if you have the following list, it would be interesting to show a csv list of employees that have each title.
SELECT e.title, c.firstname + ' ' + c.lastname
FROM [AdventureWorks].[HumanResources].[Employee] e
INNER JOIN [AdventureWorks].[Person].[Contact] c
ON c.contactid = e.contactid
ORDER BY e.title;
But I want it to look like this…
You can do it with a sub query in the list of columns.
SELECT e.title,
( SELECT Substring((SELECT ', ' + c1.firstname + ' ' + c1.lastname
FROM [AdventureWorks].[HumanResources].[Employee] e1
INNER JOIN [AdventureWorks].[Person].[Contact] c1
ON c1.contactid = e1.contactid
WHERE e1.title = e.title
ORDER BY c1.lastname
FOR XML PATH('')),3,10000000) AS list ) AS employeelist
FROM [AdventureWorks].[HumanResources].[Employee] e
GROUP BY e.title;
So even if you can do the CSV formatting in a web page before it is displayed to the user, it may be easier to do it in the database like this.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply