如何连接多行？

发布于 2024-09-07 06:17:56 字数 823 浏览 5 评论 0原文

我有以下查询，它返回所有员工的工资。这工作完美，但我需要收集额外的数据，并将其聚合到一个单元格中（请参阅结果集 2）。

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

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

原文

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).

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

Result Set 1

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

I need:

Result Set 2

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情场扛把子 2024-09-14 06:17:56

对于 SQL Server 2005+，使用 STUFF 函数和 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

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

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...

回复收藏 0 原文

九厘米的零° 2024-09-14 06:17:56

我面前没有我的代码，或者我会向您展示一个简单的示例，但我会考虑为此编写一个 CLR 聚合。非常简单。有一些自动创建的方法可供使用，它们仅用于收集（添加到 List<> 对象或其他内容）、合并（合并在多个线程中创建的多个列表）和输出（获取列表并将其翻转）转换为字符串 - String.Join(",", list.ToArray()))。唯一需要知道的是，长度限制为 8000 个字符。

回复收藏 0 原文

~没有更多了~