Error converting data type varchar to bigint when trying to concatenate column row values

I have the following query that I am using to concatenate the row values of a column into a single value.

SELECT
    SUBSTRING(
        ( 
            SELECT
                ',' + id_number AS 'data()'
            FROM
                users FOR XML PATH('')
        ),
        2,
        9999
    ) AS id_numbers

The id_number column is of type varchar. I have tried to convert to text but still getting errors.

Answer

id_number is a bigint, you’ll need to CONVERT or CAST it to a text type:

SELECT
    SUBSTRING(
        ( 
            SELECT
                ',' + CONVERT( varchar(20), id_number ) AS 'data()'
            FROM
                users FOR XML PATH('')
        ),
        2,
        9999
    ) AS id_numbers

If you’re using SQL Server 2017 or later you can use STRING_AGG (MS SQL Server’s equivalent to MySQL’s GROUP_CONCAT):

SELECT
    STRING_AGG( id_number, ', ' )

(SQL Server will handle the implicit bigint-to-varchar conversion for you as there’s no ambiguousness introduced by the use of binary operators).