Create an inline function
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'fn_ConcatStrings')
DROP FUNCTION fn_ConcatStrings
GO
CREATE FUNCTION fn_ConcatStrings
(@ID int)
RETURNS varchar(500)
AS
BEGIN
declare @String varchar(500)
select @String = coalesce(@String,'') + Comment + ','
from mytable
where [Id] = @ID
if Right(@String,1) = ','
set @String = substring(@String,1,len(@String) -1)
return @String
END
GO
then execute
select ID, dbo.fn_ConcatStrings(ID)
From (Select distinct ID from Mytable) as DT