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? — Taryn
– Taryn, Commented Dec 5, 2012 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
– radicalbiscuit, Commented Dec 5, 2012 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
– Esoteric Screen Name, Commented Dec 5, 2012 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
– radicalbiscuit, Commented Dec 5, 2012 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
– Esoteric Screen Name, Commented Dec 5, 2012 at 20:24

Taryn · Accepted Answer · 2012-12-05 19:57:51Z

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 5, 2012 at 19:57

answered Dec 5, 2012 at 19:47

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SPFiredrake Over a year ago

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 Over a year ago

No, I mean you don't need the ,TYPE).value('.', 'NVARCHAR(MAX)') part after the FOR XML PATH. See sqlfiddle.com/#!3/2e1f4/9

Taryn Over a year ago

@SPFiredrake fixed. :) Its been a long day

Andrew Barber · Accepted Answer · 2013-01-30 22:36:55Z

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, 2013 at 22:36

Andrew Barber

40.3k20 gold badges97 silver badges124 bronze badges

answered Dec 5, 2012 at 19:35

Aaron Kempf

5882 silver badges11 bronze badges

2 Comments

radicalbiscuit Over a year ago

Please use the table names and column names from the given examples.

radicalbiscuit Over a year ago

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!

ajp · Accepted Answer · 2012-12-05 19:34:11Z

0

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

answered Dec 5, 2012 at 19:34

ajp

1,4902 gold badges13 silver badges25 bronze badges

Collectives™ on Stack Overflow

SELECT multiple rows from single column into single row

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related