从单列选择多行到单行 [英] SELECT multiple rows from single column into single row

问题描述

我想编写一个 SQL Server 查询来从以下示例表中检索数据:

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

如您所见，SkillLink 表的目的是将各种(可能是多个或没有)技能与个人匹配.我想用我的查询实现的结果是:

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.

推荐答案

为此，您可以使用 FOR XML PATH:

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

参见SQL Fiddle with Demo

结果:

| NAME |            SKILLS |
----------------------------
| Bill | Telepathy, Karate |
|  Bob |            (null) |
|  Jim |         Carpentry |

这篇关于从单列选择多行到单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！