SQL Server: how to remove last comma after concatenating strings using XML path

Question

I found a way to combine multiple lines into a single line, separated by a comma, 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', '[e-mail]'),
       ('Twitter', '@sqlfiddle');

This query concatenates types, but I want to 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 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 when I controlled the visualization code. I would teach the caller to handle a comma. You must also allow null and 4K or 8K SQL row limits.

Answer 2

As long as you already have an answer, another common idiom that you will see:

select stuff((
    SELECT top (2) 
        ', ' type AS 'data()'
    FROM  
        supportContacts
    ORDER BY 
        type DESC
    FOR XML PATH('')
), 1, 2, '')

: " , 1, ".

Answer 3

→

1.

2. Use of materials for disposal

select (stuff((
   SELECT ', '+ Name  AS 'data()' 
   FROM Table_1 
   FOR XML PATH('')),
   Count('ID')
, 1, ' '))as Result