Use Left Join Alias in Column Select in SQL Views

Question

I am working on creating a view in SQL server one of the columns for which needs to be a comma separated value from a different table. Consider the tables below for instance -

CREATE TABLE Persons
(
    Id INT NOT NULL PRIMARY KEY,
    Name VARCHAR (100)
)

CREATE TABLE Skills
(
    Id INT NOT NULL PRIMARY KEY,
    Name VARCHAR (100),
)

CREATE TABLE PersonSkillLinks
(
    Id INT NOT NULL PRIMARY KEY,
    SkillId INT FOREIGN KEY REFERENCES Skills(Id),
    PersonId INT FOREIGN KEY REFERENCES Persons(Id),
)

Sample data

INSERT INTO Persons VALUES
(1, 'Peter'),
(2, 'Sam'),
(3, 'Chris')


INSERT INTO Skills VALUES
(1, 'Poetry'),
(2, 'Cooking'),
(3, 'Movies')

INSERT INTO PersonSkillLinks VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1)

What I want is something like shown in the image

While I have been able to get the results using the script below, I have a feeling that this is not the best (and certainly not the only) way to do as far as performance goes -

CREATE VIEW vwPersonsAndTheirSkills
AS
    SELECT p.Name,
    ISNULL(STUFF((SELECT ', ' + s.Name FROM Skills s JOIN PersonSkillLinks psl ON s.Id = psl.SkillId WHERE psl.personId = p.Id FOR XML PATH ('')), 1, 2, ''), '') AS Skill
    FROM Persons p 
GO

I also tried my luck with the script below -

CREATE VIEW vwPersonsAndTheirSkills
AS
 SELECT p.Name,
 ISNULL(STUFF((SELECT ', ' + skill.Name FOR XML PATH ('')), 1, 2, ''), '') AS Skill
 FROM persons p
 LEFT JOIN 
 (
    SELECT s.Name, psl.personid FROM Skills s
    JOIN PersonSkillLinks psl ON s.Id = psl.SkillId
 ) skill ON skill.personId = p.Id

GO

but it is not concatenating the strings and returning separate rows for each skill as shown below -

So, is my assumption about the first script correct? If so, what concept am I missing about it and what should be the most efficient way to achieve it.

Answer 1

I would try with APPLY :

SELECT p.Name, STUFF(ss.skills, 1, 2, '') AS Skill
FROM Persons p OUTER APPLY
     (SELECT ', ' + s.Name 
      FROM Skills s JOIN 
           PersonSkillLinks psl 
           ON s.Id = psl.SkillId 
      WHERE psl.personId = p.Id 
      FOR XML PATH ('')
     ) ss(skills);

By this way, optimizer will call STUFF() once not for all rows returned by outer query.