;WITH TestData AS
(
	SELECT	*
	FROM	(
		VALUES
			(N'กก', 1), (N'ขข', 1), (N'คค', 1),
			(N'aa', 2), (N'bb', 2),
			(N'ZZ', 3)
	) AS TestData([Name], CategoryId)
), UniqueCategory AS
(
	SELECT	DISTINCT CategoryId
	FROM	TestData
)

---- Debug Data
--SELECT	*
--FROM	TestData
--ORDER BY	CategoryId, [Name]

--SELECT	*
--FROM	UniqueCategory
--ORDER BY	CategoryId

-- Test Case
SELECT	a.[CategoryId],

		STUFF(
		(
			SELECT ', ' + b.[Name]
			FROM	TestData AS b
			WHERE	a.CategoryId = b.CategoryId
			ORDER BY	CategoryId
			FOR XML PATH(''), TYPE
		).value('.[1]', 'nvarchar(max)'), 1, 1, ''
	) AS [Names]

FROM	UniqueCategory AS a