CODEWITHSUNDEEP
sqlsql-server
radicalbiscuit
radicalbiscuit

Reputation: 487

SELECT multiple rows from single column into single row

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.

Upvotes: 2

Views: 54055

Answers (3)

Aaron Kempf
Aaron Kempf

Reputation: 588

What you are looking for is something like SQL Server's FOR XML PATH('') which combines results as a rowset

Select Person.Name, 
  (
     Select SkillName + ',' 
     From SkillLink 
     inner join skill on skill.id = skilllink.skillid
     Where SkillLink.PersonID = Person.ID FOR XML PATH('')
  )
as Skills
FROM Person

Upvotes: 6

Taryn
Taryn

Reputation: 247700

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 |

Upvotes: 15

ajp
ajp

Reputation: 1490

What you are looking for is something like SQL Server's FOR XML PATH which combines results as a rowset

Upvotes: 0

Related Questions