string agg
After sql server 2016 there is an aggregation function string_agg() which is equivalent to oracle's list_agg()
For earlier versions including 2016, to concatenate a list of values, use the following script.
The stuff() function is for replacing the first comma with ''.
SELECT
STUFF
(
(
SELECT ',' + name AS [text()]
FROM
(
select 'abc' as name
union
select '123'
)s
FOR XML PATH('')
)
,1,1,''
)