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!