sql - 如何在一列中加入值？ (SQL Server 2008R2)

Question

我不知道如何在标题中简短地描述我的问题，但我希望有人能理解并能帮助我 :)

在我的例子中，我加入了 2 个表:

SELECT t1.Name, t1.Group
FROM tblOne AS t1
UNION
SELECT t2.Name, t2.Group
FROM tblTwo AS t2

结果:

=====================
Name     |     Group
=====================
Miller   |     TST
Miller   |     DEV
Johnson  |     TST
White    |     TST
Lopez    |     DEV
Brown    |     TST
Jackson  |     DEV
Jackson  |     TST

目标:

Name     |     Group
=====================
Miller   |     DEV, TST
Johnson  |     TST
White    |     TST
Lopez    |     DEV
Brown    |     TST
Jackson  |     DEV, TST

有人有想法吗？预先感谢您提供任何提示。

Answer 1

您可以使用非常简单的 xml 方法和 stuff() 函数:

select t1.name, stuff((select distinct ','+t2.[group] 
                       from table2 t2
                       where t2.name = t1.name
                       for xml path('')
                       ), 1, 1, ''
                     ) as [group]
from table1 t1 
group by name;