;WITH SampleData (ID,Fruit)

AS

(

SELECT 1,'Apple' UNION ALL

SELECT 2,'Orange' UNION ALL

SELECT 3,'Pear' UNION ALL

SELECT 4,'Grape' UNION ALL

SELECT 3,'Pear' UNION ALL

SELECT 5,'Banana' UNION ALL

SELECT 6,'Lime'

)

SELECT

STUFF((SELECT DISTINCT ','+s.Fruit AS [text()]

FROM

SampleData AS s

FOR XML PATH('')

), 1, 1, '' )