SQL 从单列选择多行到单行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13730484/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT multiple rows from single column into single row
提问by radicalbiscuit
I want to write an SQL Server query that will retrieve data from the following example tables:
我想编写一个 SQL Server 查询，该查询将从以下示例表中检索数据：
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:
如您所见，SkillLink 表的目的是将各种（可能是多个或没有）技能与个人匹配。我想用我的查询实现的结果是：
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.
因此，对于每个人，我想要一个逗号连接的所有指向他的 SkillName 的列表。这可能是多个技能，也可能根本没有。
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.
也请随时为这个问题建议一个更好的标题作为评论，因为简洁地表达它是我的问题的一部分。
回答by Taryn
You would use FOR XML PATHfor this:
你会用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
Result:
结果：
| NAME | SKILLS |
----------------------------
| Bill | Telepathy, Karate |
| Bob | (null) |
| Jim | Carpentry |
回答by Aaron Kempf
What you are looking for is something like SQL Server's FOR XML PATH('') which combines results as a rowset
您正在寻找的是类似于 SQL Server 的 FOR XML PATH('') 的东西，它将结果组合为一个行集
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
回答by ajp
What you are looking for is something like SQL Server's FOR XML PATH which combines results as a rowset
您正在寻找的是类似于 SQL Server 的 FOR XML PATH 的东西，它将结果组合为一个行集
