Combine rows to columns using COALESCE

Suppose we have a table as under

declare @t table(somecolumn varchar(MAX))
insert into @t
    select 'X' union all
    select 'Y' union all
    select 'Z' union all
    select 'R' union all
    select 'S'

We want the result to be

CombinedResult
X, Y, Z, R, S

We can achieve this with COALESCE

DECLARE @SomeColumnList VARCHAR(8000);
SELECT @SomeColumnList =
    COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20)) 
FROM @t T1
SELECT CombinedResult = @SomeColumnList

We are merging the rows into columns by using the COALESCE function and then storing the result of every wor into the variable @SomeColumnList