DevConnections – SQL Server Track

This week I am in Las Vegas attending the DevConnections conference on the SQL Server track.

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 seperated 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.