Convert rows into a "string of columns"

[and...@webmail.co.za]

Using MSSQL 2008 R2 this query:

SELECT tblusers.id
FROM tblusers
WHERE tblusers.name = 'smith'

gives me a few rows:

id
123
678
987

How do I convert that column into a string containing a coma separated list of the id's? Example:

123,678,987

Thanks

[Erland Sommarskog]

; WITH CTE (list) AS (
SELECT ltrim(str(id)) + ','
FROM tblusers
WHERE name = 'smith'
FOR XML PATH('')
)
SELECT substring(list, 1, len(list) - 1)
FROM CTE


--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Andy Dufresne]

> ; WITH CTE (list) AS (
> SELECT ltrim(str(id)) + ','
> FROM tblusers
> WHERE name = 'smith'
> FOR XML PATH('')
> )
> SELECT substring(list, 1, len(list) - 1)
> FROM CTE

Thanks Erland. I was thinking along the lines of a CTE... Very powerful, if you know how to do it! Thanks.