Unable to Concatenate many rows into a single text string sql server 2008

Question

Is there another way to do this without the XML function? I've tried following Concatenate many rows into a single text string? I'm having issues trying to run this on SQL Server 2008 R2.

Conversion failed when converting the varchar value ',' to data type int.

Select distinct ST2.id 
    substring(
        (
            Select ','+ST1.id AS [text()]
            From dbo.idstable ST1
            Where ST1.id = ST2.id
            ORDER BY ST1.id
            For XML PATH ('')
        ), 2, 1000) [idstable]
From dbo.idstable ST2

INTs need to be CAST to nvarchar...As in answer by @Dan Guzman — Grantly, Jan 15 '18 at 16:31

score 1 · Accepted Answer · answered Jan 15 '18 at 16:29

1

The reason for the syntax error is the extraneous column name after DISTINCT. Below is a similar method that uses STUFF and TYPE to avoid issues with special characters in the string.

SELECT DISTINCT
    STUFF(
        (
            SELECT ','+CAST(ST1.id AS varchar(10)) AS [text()]
            FROM dbo.idstable ST1
            WHERE ST1.id = ST2.id
            ORDER BY ST1.id
            FOR XML PATH (''), TYPE
        ).value('.', 'nvarchar(MAX)'), 1, 1, '') [Students]
FROM dbo.idstable ST2;

The other supported method for aggregate string concatenation on older SQL versions is a cursor.

answered Jan 15 '18 at 16:29

Dan Guzman

43,250
3
46
71

thanks i'll try this. – Sql_Pete_Belfast Jan 15 '18 at 16:31
Unfortunately, I can't use a cursor as I need this to build a persistent derived table in looker BI tool and the cursor functionality doesn't work. – Sql_Pete_Belfast Jan 15 '18 at 16:32
@Sql_Pete_Belfast, actually it's probably a good thing you can't use cursors :-) – Dan Guzman Jan 15 '18 at 16:33
Thanks! This is working! – Sql_Pete_Belfast Jan 15 '18 at 16:34
@Sql_Pete_Belfast, glad to help. Don't forget to mark this as the answer so that others with similar questions can find the solution easier. – Dan Guzman Jan 15 '18 at 16:38

score 1 · Answer 2 · answered Jan 15 '18 at 16:48

Dan's answer is correct, as far as it goes. From a performance perspective, it is better to do the distinct in a subquery (and probably to include the id in the outer query:

SELECT id
       STUFF( (SELECT ',' + CAST(ST1.id AS varchar(10)) AS [text()]
               FROM dbo.idstable ST1
               WHERE ST1.id = ST2.id
               ORDER BY ST1.id
               FOR XML PATH (''), TYPE
              ).value('.', 'nvarchar(MAX)'
                     ), 1, 1, '') as [Students]
FROM (SELECT DISTINCT id dbo.idstable) ST2;

Unable to Concatenate many rows into a single text string sql server 2008

2 Answers2