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,

Industrialize

In a pinch, you can use substring, to delete the last character ;) – Superload

@Superload will that work if I don't know how long the string will be? Can you show me an example? – Industrialize

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

Hysterics

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

Viscountess

I strongly prefer using STUFF than trying to combine LTRIM/RTRIM/LEN/etc. – Hickerson

Answer 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

Gregorygregrory