SQL Server: how to remove last comma after combining rows using XML Path
16,032
Solution 1
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
Solution 2
While you already have an answer, another common idiom that you'll see is:
select stuff((
SELECT top (2)
', ' type AS 'data()'
FROM
supportContacts
ORDER BY
type DESC
FOR XML PATH('')
), 1, 2, '')
This says "take the result of the select and replace the two characters starting at position 1 with a zero-length string".
Solution 3
This works for me->
1.Inserting comma Before Data
2.Using Stuff to Remove it
select (stuff((
SELECT ', '+ Name AS 'data()'
FROM Table_1
FOR XML PATH('')),
Count('ID')
, 1, ' '))as Result
Author by
MattJ
Updated on July 22, 2022Comments
-
MattJ almost 2 years
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, type varchar(20), details varchar(30) ); INSERT INTO supportContacts (type, details) VALUES ('Email', '[email protected]'), ('Twitter', '@sqlfiddle');
This query combines types, but I want to now remove the last comma:
SELECT top (2) type + ', ' AS 'data()' FROM supportContacts ORDER BY type DESC FOR XML PATH('')
This is the current result:
Twitter, Email,
-
Jason W over 9 yearsI strongly prefer using STUFF than trying to combine LTRIM/RTRIM/LEN/etc.