MS SQL Tips & Tricks

How to convert table values to comma-delimited string

Sometime there is a need to get list of IDs of some records set and return the separated by coma. The simpliest way is to use the loop, but it is not to efficient. The example below shows how to gain the goal using internal MSSQL's XML mechanizms. It works with MS SQL 2005 and above.

Dict Table:

ID Name Item
1 Countries Poland
2 Countries United Kingdom
3 Countries Scotland
4 Countries Canada
5 Countries USA
6 Jobs Tracher
7 Jobs Mechanic
8 Jobs Developer

 

SELECT
Name, REPLACE(
	    RTRIM(
		 (SELECT cast(Id as varchar(max)) + '|' 
		  FROM Dict WHERE (Name = i.Name)
		  FOR XML PATH (''))
		),'|',', ') AS ItemIDs
FROM Dict i
GROUP BY Name

The query above, will give you result of:

Name ItemIDs
Countries 1, 2, 3, 4, 5,
Jobs 6, 7, 8,

To get the item names instead of IDs you can do small change in a code above:

SELECT
Name, REPLACE(
	    RTRIM(
		 (SELECT cast(Item as varchar(max)) + '|' 
		  FROM Dict WHERE (Name = i.Name)
		  FOR XML PATH (''))
		),'|',', ') AS ItemIDs
FROM Dict i
GROUP BY Name

It will give you result of:

Name ItemIDs
Countries Poland, United Kingdom, Scotland, Canada, USA,
Jobs Tracher, Mechanic, Developer,

Download SQL script

See also


Learn more tricks

Add Comment

Comments

27th July 2009

"... but it is not to eficient ..." How do you know? Maybe you wanted to use the word "attractive"?

jaro