要连接的 Sum 的等效字符串 [英] string equivalent of Sum to concatenate

问题描述

我希望查询在 1 行输出左表中的 Id 和连接表中的描述.

I would like a query to output on 1 line the Id from the left table and the descriptions from a joined table.

架构:

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

推荐答案

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 ║
╚══════════╩════════════════════════════╝

这篇关于要连接的 Sum 的等效字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！