You may want to visit this extension post from @SQLZealot that deals with generating grouped comma-separated strings: http://beyondrelational.com/blogs/sqlzealot/archive/2012/01/02/tsql-script-generating-concatenating-values-into-a-comma-separated-string-with-a-grouping.aspx

Another option is to use PIVOT. Not nearly as flexible as the FOR XML PATH('') solution

DECLARE @NamesTable TABLE (Id INT, Name NVARCHAR(50))

INSERT INTO @NamesTable (Id, Name)
VALUES 
    (1, 'A'), 
    (2, 'D'), 
    (2, 'C'), 
    (3, 'E'), 
    (3, 'H'), 
    (3, 'G')

SELECT
    ISNULL(CAST([1] as varchar(max)) + ',', '') + 
    ISNULL(CAST([2] as varchar(max)) + ',', '') + 
    ISNULL(CAST([3] as varchar(max)) + ',', '') + 
    ISNULL(CAST([4] as varchar(max)) + ',', '') + 
    ISNULL(CAST([5] as varchar(max)) + ',', '') + 
    ISNULL(CAST([6] as varchar(max)), '') 
FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY Name) as RowNum, Name FROM @NamesTable) Base
    PIVOT
    	(
    		MIN(Name)
    		FOR RowNum IN ([1],[2],[3],[4],[5], [6])
    	) pvt

Using CTE you can avoid using variable declaration.

;WITH CTE(x, name )
AS
(SELECT ROW_NUMBER() OVER (ORDER BY Id ), NAME FROM @NamesTable )
,CSV(X,Result)
AS
(SELECT x, CONVERT(varchar(max),NAME) FROM CTE WHERE x = 1
UNION ALL 
SELECT CSV.x+1, CONVERT(varchar(max),Result +',' + NAME )
FROM CSV 
INNER JOIN CTE 
ON CSV.X+1 = CTE.x )
SELECT Result FROM CSV T1
WHERE NOT exists (SELECT 1 FROM CSV T2 WHERE T1.X < T2.X )

Another possibility is to avoid both the variable AND the CTE:

SELECT STUFF((SELECT (',' + nt.Name)
          FROM @NamesTable nt
          FOR XML PATH('')), 1, 1, '');

Great feedback, everyone! Thank-you very much!

@marc_jellinek: PIVOT is possible, but here's the issue. Conventional PIVOT would work great for a fixed number of strings. The option is to use dynamic PIVOT, which would be perhaps be the costliest of all operations. @Eric Bradford: Great idea on using the CTE. I will try it out and compare it with the others from a performance perspective. @Aaron N. Cutshall: Using XML is a good option, but I find it to be costly for this task. It does make for a compact, clean code, though.

Keep the feedback coming, and have a nice day!

Nakul: Your comment about XML is valid enough and the STUFF operation on top of that does take additional time. I agree that the COALESCE option is the most efficient but only when you have the luxury to use a variable. In most cases when I've had a similar need I needed the results to be from a query and the variable option was not possible.

@Aaron N. Cutshall: Absolutely - if the luxury of using a variable is not available, then using XML operations is a good approach.