SQL comma separated values

Question

I am using this query in another big query. I need this query to return comman separated values (so John,David,Brian,Jake) and without using any intermediate variable.

I'm using SQL Server 2014.

Could you please provide necessary SQL?

SELECT
    --@listStr = (COALESCE(@listStr + ',', '') + ShortName)
X.ShortName
FROM
    (
    SELECT 'John' AS ShortName UNION ALL
    SELECT 'David' AS ShortName UNION ALL
    SELECT 'Brian' AS ShortName UNION ALL
    SELECT 'Jake' AS ShortName 
    ) X

Is SQL your only option or could you concatenate the results in the app/reporting layer - I only ask because I hate the current ways that are available (cursors, `STUFF`/`FOR XML`, etc.) because they either are too complicated to understand or don't perform as well. It is _much_ simpler to accomplish in an app or report. — D Stanley, Nov 05 '14 at 22:11
possible duplicate of [Comma Separated values with SQL Query](http://stackoverflow.com/questions/12671117/comma-separated-values-with-sql-query) — Karl Kieninger, Nov 05 '14 at 22:15

radar · Accepted Answer · 2014-11-05T22:23:52.117

0

you can use STUFF function, FOR XML PATH can be used to concatenate the values with comma as separator and STUFF removes the leading comma.

SELECT STUFF((SELECT  ',' + ShortName
    FROM
    (
    SELECT 'John' AS ShortName UNION ALL
    SELECT 'David' AS ShortName UNION ALL
    SELECT 'Brian' AS ShortName UNION ALL
    SELECT 'Jake' AS ShortName 
    ) X
    FOR XML PATH('')),1,1,'' )

edited Nov 05 '14 at 22:23

answered Nov 05 '14 at 22:06

radar

13,270
2
25
33

"Use the `STUFF` function" is a gross simplification of this complex method. Are you able to explain _how_ this works? – D Stanley Nov 05 '14 at 22:10
@DStanley, good point, i saw that OP was proficient in SQL to understand this, but will keep in mind as it is useful to others. – radar Nov 05 '14 at 22:24

SQL comma separated values

1 Answers1