将查询中的行与相同的id组合起来

Question

这是我的疑问

select * 
from Owner_TABLE

结果：

RollNumber         People_ID   Owner
-------------------------------------------
444201000100100      12        Jame Bond
444201000100100      14        Sam Doris
444201000100200      16        Jane Doe
444201000100200      17        John Morris
444201000100300      18        Mandy Noor

我在这里的目标是计算出如何使用同一个RollNumber将所有者组合成一行。

就像=>

RollNumber         Owner
----------------------------------------
444201000100100    James Bond, Sam Doris

这个是可能的吗？

Answer 1

嗨，我只是想和大家分享一下这个剧本，谢谢那些贴出他们建议的人。

select p1.RollNumber,
(SELECT  FullName+', '  
FROM [dbo].[view_Owners_roll] p2 
WHERE
p1.RollNumber=p2.RollNumber 
for XML path(''),type).value('.','varchar(max)') 
as Name  from [dbo].[view_Owners_roll] p1 group by RollNumber

Answer 2

为了举例说明这些评论所指的是什么：

WITH ex as
(SELECT '444201000100100' as 'RollNumber', '12' as 'PeopleID', 'Jame Bond' as 'Owner'
UNION
SELECT '444201000100100', '14', 'Sam Doris'
UNION
SELECT '444201000100200', '16', 'Jane Doe'
UNION
SELECT '444201000100200', '17', 'John Morris'
UNION
SELECT '444201000100100', '18', 'Mandy Noor'
) 
SELECT RollNumber, STUFF((SELECT ', ' + e.Owner
                          FROM ex e
                          WHERE e.RollNumber = a.RollNumber
                          FOR XML PATH('')
                          ),1,2,'') as 'Owners'
FROM ex a
GROUP BY RollNumber

STUFF()移除剩余的额外逗号。FOR XML子句格式化结果set...for XML。

尝试这样做可以更好地感受到FOR XML PATH：

SELECT Owner
FROM ex
FOR XML PATH('')