合并行值 T-SQL

Question

我正在试图为一份报告整理一些数据，需要将其中一个表的行值连接起来。以下是基本表格结构： 评论

 ReviewID  
 ReviewDate  

审阅者

 ReviewerID  
 ReviewID  
 UserID  

用户

UserID  
FName  
LName  

这是一种多对多的关系。每个评论可以有多个评论者;每个用户可以与多个评论相关联。

基本上，我想要看到的是Reviews.ReviewID、Reviews.ReviewDate和与该评论相关联的所有用户的FName的串联字符串(逗号分隔)。

而不是:

ReviewID---ReviewDate---User  
1----------12/1/2009----Bob  
1----------12/1/2009----Joe  
1----------12/1/2009----Frank  
2----------12/9/2009----Sue  
2----------12/9/2009----Alice  

显示这个：

ReviewID---ReviewDate----Users  
1----------12/1/2009-----Bob, Joe, Frank  
2----------12/9/2009-----Sue, Alice

我找到了这篇文章，介绍了一些方法来实现这个目的，但大部分似乎只处理一个表，而不是多个表；不幸的是，我的SQL技术水平不足以将它们适应我的情况。我对该网站上使用 FOR XML PATH() 的示例特别感兴趣，因为看起来最简洁和最直接。

SELECT p1.CategoryId,
( SELECT ProductName + ', '
  FROM Northwind.dbo.Products p2
  WHERE p2.CategoryId = p1.CategoryId
  ORDER BY ProductName FOR XML PATH('')
) AS Products
FROM Northwind.dbo.Products p1
GROUP BY CategoryId;

有人可以帮我一下吗？非常感谢您的任何帮助!

- Sesame

---

类似于https://dev59.com/anVD5IYBdhLWcg3wAGiD和https://dev59.com/CXRB5IYBdhLWcg3w9b99。 - VolkerK

Answer 1

看一下这个

DECLARE @Reviews TABLE(
        ReviewID INT,
        ReviewDate DATETIME
)

DECLARE @Reviewers TABLE(
        ReviewerID   INT,
        ReviewID   INT,
        UserID INT
)

DECLARE @Users TABLE(
        UserID  INT,
        FName  VARCHAR(50),
        LName VARCHAR(50)
)

INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'

INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''

INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5

SELECT  *,
        ( 
            SELECT  u.FName + ','
            FROM    @Users u INNER JOIN 
                    @Reviewers rs ON u.UserID = rs.UserID
            WHERE   rs.ReviewID = r.ReviewID
            FOR XML PATH('')
        ) AS Products
FROM    @Reviews r

- Adriaan Stander

---

这段代码无法正确处理 XML 特殊字符，如 > 和 &。因此，如果数据包含 Frank & Bill，则在结果集中会得到 Frank & Bill。有一种巧妙的处理方法，请参见：https://dev59.com/Q2445IYBdhLWcg3wH2rH#5031297 - KM.

Answer 2

原来有一种更简单的方法可以做到这一点，不需要使用UDF：

select replace(replace(replace((cast((
        select distinct columnName as X
        from tableName 
        for xml path('')) as varchar(max))), 
   '</X><X>', ', '),'<X>', ''),'</X>','')

- Digital Mindspring

---

这是我见过的最干净的方法，不需要UDF。在我的情况下速度非常快。谢谢！ - AaronShockley

我一直在寻找这个纯金片段，谢谢。 - John Zabroski

Answer 3

我遇到了类似的问题，在花费15分钟尝试编码后找到了一个好的解决方案。

declare @result varchar(1000)
select @result = COALESCE(@result+','+A.col1, A.col1)
                FROM (  select  col1
                        from [table] 
                ) A
select @result

将结果作为value1，value2，value3，value4返回

享受 ;)

- Talha

---

干得好 - 非常整洁！@Sesame，我建议将采纳的答案改为这个！ - Shaul Behr

这是不被Microsoft支持的，可能会产生意想不到的结果。请参阅https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/或我的博客http://marc.durdin.net/2015/07/concatenating-strings-in-sql-server-or-undefined-behaviour-by-design/以获取更多讨论。 - Marc Durdin

Answer 4

SqlServer 2017现在有STRING_AGG函数，它可以使用给定分隔符将多个字符串合并为一个。

- John

Answer 5

我已经处理过滚动数据的三种方法，如您所述，1.使用游标，2.使用UDF或3.使用.NET CLR编写的自定义聚合函数。
游标和UDF相当慢（每行约0.1秒）。CLR自定义聚合函数非常快（每行约0.001秒）。

Microsoft在SQL 2005的SDK中提供了完全符合您要求的代码。如果您已安装，则应该能够在此文件夹中找到代码： C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities。 您可能还想阅读MSDN中的这篇文章。它介绍了如何安装和启用自定义聚合函数： http://msdn.microsoft.com/en-us/library/ms161551(SQL.90).aspx

编译并安装自定义聚合函数后，您应该能够像这样查询：

SELECT Reviews.ReviewID, ReviewDate, dbo.StringUtilities.Concat(FName) AS [User]
FROM Reviews INNER JOIN Reviewers ON Reviews.ReviewID = Reviewers.ReviewID
   INNER JOIN Users ON Reviews.UserID = Users.UserID
GROUP BY ReviewID, ReviewDate;

并获得像您展示的结果集（如上所示）

- TimG

---

+1..不幸的是，这需要涉及CLR相关内容。(如果UDF可以成为自定义聚合函数的目标就好了:-/) - user166390

Answer 6

现在从SQL Server 2017开始，有一个名为STRING_AGG的新T-SQL函数：
它是一个新的聚合函数，可将字符串表达式的值连接起来，并在它们之间放置分隔符值。
分隔符不会添加到字符串的末尾。

示例：

SELECT STRING_AGG ( ISNULL(FirstName,'N/A'), ',') AS csv 
FROM Person.Person; 

结果集：

John,N/A,Mike,Peter,N/A,N/A,Alice,Bob

- Wael Ali

Answer 7

select p1.Availability ,COUNT(*),
(select  name+','  from AdventureWorks2008.Production.Location p2 where 
p1.Availability=p2.Availability for XML path(''),type).value('.','varchar(max)') 
as Name  from AdventureWorks2008.Production.Location p1 group by Availability

结果

Availability  COUNT     Name  
---------------------------------------------------------------------------------
0.00    7   Tool Crib,Sheet Metal Racks,Paint Shop,Paint Storage,Metal 
                    Storage,Miscellaneous Storage,Finished Goods Storage,
80.00   1   Specialized Paint,
96.00   1   Frame Forming,
108.00  1   Frame Welding,
120.00  4   Debur and Polish,Paint,Subassembly,Final Assembly,

- pradeep

Answer 8

一个 UDF（用户定义的函数）可以解决这个问题。只需定义一个 T-SQL 函数（UDF），该函数需要一个 int 参数（产品 ID），并返回字符串（与产品相关联的名称串联）。如果您的方法名为 GetProductNames，则查询可能如下所示：

SELECT p1.CategoryId, dbo.GetProductNames(p1.CategoryId)
FROM Northwind.dbo.Products p1
GROUP BY CategoryId

- Paul Sasik

---

那个UDF会是什么样子？我真的觉得这不是必要的。 - marc_s

@marc：是的，UDF仅仅是解决这个问题的100种方法之一。我认为这是向SQL新手展示的一个好的解决方案。 - Paul Sasik

UDF的问题在于它要么需要使用动态SQL，要么存在于每个查询类型中.. SQL Server支持自定义CLR聚合函数.. 但需要使用额外的CLR程序集.. - user166390

Answer 9

试试这个：

 Declare @Revs Table 
 (RevId int Priimary Key Not Null,
  RevDt DateTime Null,
  users varChar(1000) default '')

 Insert @Revs (RevId, RevDt)
 Select Distinct ReviewId, ReviewDate
 From Reviews
 Declare @UId Integer
 Set @Uid = 0
 While Exists (Select * From Users
               Where UserID > @Uid)
 Begin
    Update @Revs Set
      users = users + u.fName + ', '
    From @Revs R 
       Join Reviewers uR On ur.ReviewId = R.RId
       Join users u On u.UserId = uR.UserId 
    Where uR.UserId = @UId
    Select @Uid = Min(UserId)
    From users
    Where UserId > @UId
  End
  Select * From @Revs

- Charles Bretana

Answer 10

Select R.ReviewID, ReviewDate
, (Select  FName + ', ' 
   from Users 
   where UserID = R.UserID 
   order by FName FOR XML PATH(')
) as [Users]
from Reviews
inner join Reviewers AS R
  On Reviews.ReviewID = R.ReviewID
Group By R.ReviewID, ReviewDate;

- JeffO