Turn a result from rows into a comma-separated string

transpose_result.sql

DECLARE @test NVARCHAR(MAX)
SELECT @test = COALESCE(@test + ', ', '') + Field FROM <Sampletable> WHERE <whereclause> AND Field IS NOT NULL
SELECT @test