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
Comments