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.
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;