Converting part of a result set to a comma separated list

Download PDF

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:

SteveStedman5
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

Your email address will not be published. Required fields are marked *

*