I found a way to combine multiple row's into one row which is comma separated but now I would like to remove the last comma.
CREATE TABLE supportContacts
id int identity primary key,
INSERT INTO supportContacts (type, details)
VALUES ('Email', '[email protected]'),
SELECT top (2)
type + ', ' AS 'data()'
FOR XML PATH('')
declare @BigStrRes8K nvarchar(4000) SELECT @BigStrRes8K = ( SELECT top (2) [type] + ', ' AS 'data()' FROM supportContacts ORDER BY type DESC FOR XML PATH('') ) SELECT LEFT(RTRIM(@BigStrRes8K), ( LEN(RTRIM(@BigStrRes8K))) - 1) as FinalNoComma
I would never do this where I controlled the render code. I would teach the caller to handle the trailing comma. Also you have to allow for nulls and the 4K or 8K limit of SQL rows