Concatenating Column Values into a Comma-Separated List

We can easily concatenate column values into a comma-separated list with a variable, but this solution we can avoid to use a variable.

PRINT '-- Concatenating Column Values into a Comma-Separated List'

GO

--#region Create my test table

begin try drop table YourTable ; end try begin catch end catch -- begin try catch

create table YourTable (colKey nvarchar(50), colVal nvarchar(1), colOrderBy int);

insert YourTable values ('a',3, 1), ('a', 1, 2), ('b',8, 3), ('b',7, 1), ('b',6, 2);

--#endregion Create my test table

 

 

GO

SELECT DISTINCT colKey

,STUFF((

       SELECT (', ' + CAST(tbDetail.colVal AS NVARCHAR))  -- you do not neew CAST if you use a string type.

       FROM YourTable tbDetail(NOLOCK)

       WHERE tbDetail.colKey = o.colKey

       ORDER BY tbDetail.colOrderBy  -- if you want, you can order by your columns

       FOR XML PATH('')

       ), 1, 2, '') MyStuffList

FROM YourTable o(NOLOCK)

Source : http://stackoverflow.com/questions/1048209/concatenating-column-values-into-a-comma-separated-list