CREATE TABLE [Fruit] ([IdFruit] [int] NOT NULL PRIMARY KEY, [FruitName] [nvarchar](50) NULL)
INSERT INTO [Fruit] VALUES ('1', 'Apple'), ('2', 'Orange'), ('3', 'Lemon')

CREATE TABLE [People] ([IdPeople] [int] NOT NULL PRIMARY KEY, [PeopleName] [nvarchar](50) NULL)
INSERT INTO [People] VALUES ('1', 'People1'), ('2', 'People2'), ('3', 'People3')

CREATE TABLE [PeopleFruit] ([IdPeopleFruit] [int] NOT NULL PRIMARY KEY, [Peopleid] [int] NULL, [Fruitid] [int] NULL)
INSERT INTO [PeopleFruit] VALUES ('1', '1','1'), ('2', '1','2'), ('3', '2','3'), ('4', '2','1'), ('5', '3','1')


SELECT PeopleName ,( SELECT FruitName + ',' AS 'data()' FROM Fruit left join PeopleFruit t2 ON Fruitid=IdFruit WHERE t1.Peopleid=t2.Peopleid FOR XML PATH('') )
FROM People left join PeopleFruit t1 ON IdPeople=Peopleid
GROUP BY PeopleName, Peopleid

SELECT PeopleName, STRING_AGG (FruitName, ',') AS FruitName
FROM Fruit left join PeopleFruit on Fruitid=IdFruit
           left join People on IdPeople=Peopleid
GROUP BY PeopleName