I was trying this the same way as Koenraad Dendievel using a function i found elsewhere, seems to work a treat with this:

CREATE FUNCTION dbo.ConcatFruit()

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Output VARCHAR(8000)

SELECT @Output = COALESCE(@Output+', ', '') + Name

FROMfruit

RETURN @Output

END

GO

SELECT TOP 1 dbo.ConcatFruit()

FROM fruit

GO

DROP FUNCTION dbo.ConcatFruit

GO