SELECT multiple rows from single column into single row

Question

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.

possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Taryn, Dec 05 '12 at 19:02
From the title you might think that, but as you see that's not quite right. I'm wanting to concatenate many rows into a single text string AND pair them with their Persons based on a third table. I'm looking for the query that will do *all* of this, not just the concatenation. — radicalbiscuit, Dec 05 '12 at 19:29
Re: question title - You could omit "SQL query", as that's evident from the tags, and change "combine" to "select". — Esoteric Screen Name, Dec 05 '12 at 19:40
Thanks for the suggestion. I've made the change, but the title still neglects to mention that I'm trying to do this using data from 3 tables. One with data about Persons, one with data about Skills, and one linking the two. I think that's integral to my question, but I don't know how to articulate it. — radicalbiscuit, Dec 05 '12 at 19:45
Don't worry about getting all that in the title. Just a general, high level outline of your problem is enough. The bit about the three tables is important to the question, but it's OK that it's not in the title, so long as it's in the question body. — Esoteric Screen Name, Dec 05 '12 at 20:24

Taryn · Accepted Answer · 2012-12-05T19:57:51.230

15

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 |

edited Dec 05 '12 at 19:57

answered Dec 05 '12 at 19:47

Taryn

242,637
56
362
405

The `.value('text()[1]','nvarchar(max)')` is unnecessary. If you're worried about not having enough space for the data type, then do a `CAST`/`CONVERT` on `s.SkillName` to `varchar(max)`. IE: `SELECT ', ' + CAST(s.SkillName AS VARCHAR(MAX))...` – SPFiredrake Dec 05 '12 at 19:56
No, I mean you don't need the `,TYPE).value('.', 'NVARCHAR(MAX)')` part after the `FOR XML PATH`. See http://sqlfiddle.com/#!3/2e1f4/9 – SPFiredrake Dec 05 '12 at 19:59
@SPFiredrake fixed. :) Its been a long day – Taryn Dec 05 '12 at 20:01

score 6 · Answer 2 · edited Jan 30 '13 at 22:36

6

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

edited Jan 30 '13 at 22:36

Andrew Barber

39,603
20
94
123

answered Dec 05 '12 at 19:35

Aaron Kempf

580
2
11

1

Please use the table names and column names from the given examples. – radicalbiscuit Dec 05 '12 at 19:38
1

This works and I really like the simplicity. However, the result includes unnecessary commas. I'm going with bluefeet's answer due to clearer writing and a cleaner result, but I wanted to thank you for giving me a working answer! – radicalbiscuit Dec 05 '12 at 20:28

score 0 · Answer 3 · answered Dec 05 '12 at 19:34

0

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

answered Dec 05 '12 at 19:34

ajp

1,440
2
13
25

SELECT multiple rows from single column into single row

3 Answers3

Linked