要连接的Sum的字符串等效项

Question

我想查询从左表中的Id和联接表中的描述在1行上输出的ID。

架构：

person
---------    
id (int)

role
-------------
id (int)
description (varchar(100))

personrole
-------------
personid (int)
roleid (int)

示例数据：

person
------------
id
1
2

role
------------
id   description
1    user
2    admininstrator
3    tester

personrole
-------------
personid   roleid
1          1
2          1
2          2
2          3

因此，我希望输出为：

PersonId   Roles
1          user
2          user;administrator;tester

Answer 1

SELECT
p.ID PersonID,
STUFF(
(SELECT ‘;’ + b.description
FROM personrole a
INNER JOIN role b
ON a.roleid = b.id
WHERE a.personid = p.id
FOR XML PATH (‘’))
, 1, 1, ‘’) AS DescriptionList
FROM person AS p
GROUP BY p.ID

• SQLFiddle演示

输出

╔══════════╦════════════════════════════╗
║ PERSONID ║      DESCRIPTIONLIST       ║
╠══════════╬════════════════════════════╣
║        1 ║ user                       ║
║        2 ║ user;admininstrator;tester ║
╚══════════╩════════════════════════════╝