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

 1PRINT '-- Concatenating Column Values into a Comma-Separated List'
 2
 3GO
 4
 5--#region Create my test table
 6
 7begin try drop table YourTable ; end try begin catch end catch -- begin try catch
 8
 9create table YourTable (colKey nvarchar(50), colVal nvarchar(1), colOrderBy int);
10
11insert YourTable values ('a',3, 1), ('a', 1, 2), ('b',8, 3), ('b',7, 1), ('b',6, 2);
12
13--#endregion Create my test table
14
15GO
16
17SELECT DISTINCT colKey
18
19,STUFF((
20
21       SELECT (', ' + CAST(tbDetail.colVal AS NVARCHAR))  -- you do not neew CAST if you use a string type.
22
23       FROM YourTable tbDetail(NOLOCK)
24
25       WHERE tbDetail.colKey = o.colKey
26
27       ORDER BY tbDetail.colOrderBy  -- if you want, you can order by your columns
28
29       FOR XML PATH('')
30
31       ), 1, 2, '') MyStuffList
32
33FROM YourTable o(NOLOCK)

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