How to serialize (to comma separated list) SQL rows

Go To StackoverFlow.com

2

I currently have a view with columns along the lines of:

ID, Name

I want to add a third column Properties which must contain a comma separated list of properties. I can get the properties from a joining table, but at the moment joining will return a separate row for each property. What's the best way of pulling a single row for a comma separated list?

In the past I think i've used a user defined function with a cursor. It'd be great if I can do this without the UDF but i'm not 100% sure. If not, i'm sure I can get rid of the cursor with a more intelligence UDF.

2012-04-04 17:04
by Lee Tickett


5

SELECT ID, Name, Properties = STUFF((
    SELECT ',' + PropertyName FROM dbo.Properties
    WHERE ID = x.ID
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
FROM dbo.ViewName AS x
GROUP BY ID, Name;
2012-04-04 17:09
by Aaron Bertrand
legend- thanks - Lee Tickett 2012-04-04 17:17
Ads