SELECT multiple rows from single column into single row
sql, sql-server
Solution
You would use `FOR XML PATH` for this:
select p.name,
Stuff((SELECT ', ' + s.skillName
FROM skilllink l
left join skill s
on l.skillid = s.id
where p.id = l.personid
FOR XML PATH('')),1,1,'') Skills
from person p
See SQL Fiddle with Demo
Result:
| NAME | SKILLS |
----------------------------
| Bill | Telepathy, Karate |
| Bob | (null) |
| Jim | Carpentry |
Problem
I want to write an SQL Server query that will retrieve data from the following example tables: ``` Table: Person ID Name -- ---- 1 Bill 2 Bob 3 Jim Table: Skill ID SkillName -- ----- 1 Carpentry 2 Telepathy 3 Navigation 4 Opera 5 Karate Table: SkillLink ID PersonID SkillID -- -------- ------- 1 1 2 2 3 1 3 1 5 ``` As you can see, the SkillLink table's purpose is to match various (possibly multiple or none) Skills to individual Persons. The result I'd like to achieve with my query is: ``` Name Skills ---- ------ Bill Telepathy,Karate Bob Jim Carpentry ``` So for each individual Person, I want a comma-joined list of all SkillNames pointing to him. This may be multiple Skills or none at all. This is obviously not the actual data with which I'm working, but the structure is the same. Please also feel free to suggest a better title for this question as a comment since phrasing it succinctly is part of my problem.