如何连接多行? [英] How to concatenate multiple rows?

问题描述

我有以下查询，它返回所有员工的工资.这项工作完美，但我需要收集额外的数据，我将这些数据汇总到一个单元格中(请参阅结果集 2).

I have the following query which returns the salary of all employees. This work perfectly but I need to collect extra data that I will aggregate into one cell (see Result Set 2).

如何将数据聚合到逗号分隔的列表中?有点像 Sum 的作用，但我需要一个字符串作为回报.

How can I aggregate data into a comma separated list? A little bit like what Sum does, but I need a string in return.

SELECT Employee.Id, SUM(Pay) as Salary
FROM Employee
INNER JOIN PayCheck ON PayCheck.EmployeeId = Employee.Id
GROUP BY Employee.Id

结果集 1

Employee.Id              Salary
-----------------------------------
          1                 150
          2                 250
          3                 350

我需要:

结果集 2

Employee.Id              Salary                 Data
----------------------------------------------------
          1                 150      One, Two, Three
          2                 250      Four, Five, Six
          3                 350      Seven

推荐答案

对于 SQL Server 2005+，使用 STUFF 函数和 FOR XML PATH:

For SQL Server 2005+, use the STUFF function and FOR XML PATH:

WITH summary_cte AS (
   SELECT Employee.Id, SUM(Pay) as Salary
     FROM Employee
     JOIN PayCheck ON PayCheck.EmployeeId = Employee.Id
 GROUP BY Employee.Id)
SELECT sc.id, 
       sc.salary,
       STUFF((SELECT ','+ yt.data
                FROM your_table yt
               WHERE yt.id = sc.id
            GROUP BY yt.data
             FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
  FROM summary_cte sc

但是您缺少有关要转换为逗号分隔字符串的数据的位置以及它与员工记录的关系的详细信息...

But you're missing details about where the data you want to turn into a comma delimited string is, and how it relates to an employee record...

这篇关于如何连接多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！