I currently have a view with columns along the lines of:
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.
SELECT ID, Name, Properties = STUFF(( SELECT ',' + PropertyName FROM dbo.Properties WHERE ID = x.ID FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') FROM dbo.ViewName AS x GROUP BY ID, Name;