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

Question

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,

Answer 1

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

Answer 2

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