Convert rows into a "string of columns"

[a***@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]

Post by a***@webmail.co.za
SELECT tblusers.id
FROM tblusers
WHERE tblusers.name = 'smith'
id
123
678
987
How do I convert that column into a string containing a coma separated
123,678,987

; 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

[Andy Dufresne]

Post by 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

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