MattJ MattJ - 9 months ago 44
SQL Question

SQL Server: how to remove last comma after combining rows using XML Path

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', 'admin@sqlfiddle.com'),
('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,

Answer Source
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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download