连接行值 T-SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1874966/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Concatenate row values T-SQL
提问by Sesame
I am trying to pull together some data for a report and need to concatenate the row values of one of the tables. Here is the basic table structure:
我正在尝试为报告汇总一些数据，并且需要连接其中一个表的行值。下面是基本的表结构：
Reviews
评论
ReviewID
ReviewDate
Reviewers
审稿人
ReviewerID
ReviewID
UserID
Users
用户
UserID
FName
LName
This is a M:M relationship. Each Review can have many Reviewers; each User can be associated with many Reviews.
这是一种 M:M 关系。每个Review可以有多个Reviewer；每个用户可以与许多评论相关联。
Basically, all I want to see is Reviews.ReviewID, Reviews.ReviewDate, and a concatenated string of the FName's of all the associated Users for that Review (comma delimited).
基本上，我想看到的只是 Reviews.ReviewID、Reviews.ReviewDate 和该评论的所有关联用户的 FName 的串联字符串（逗号分隔）。
Instead of:
代替：
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
Display this:
显示这个：
ReviewID---ReviewDate----Users
1----------12/1/2009-----Bob, Joe, Frank
2----------12/9/2009-----Sue, Alice
I have found thisarticle describing some ways to do this, but most of these seem to only deal with one table, not multiple; unfortunately, my SQL-fu is not strong enough to adapt these to my circumstances. I am particularly interested in the example on that site which utilizes FOR XML PATH() as that looks the cleanest and most straight forward.
我发现这篇文章描述了一些方法来做到这一点，但其中大多数似乎只处理一张表，而不是多张表；不幸的是，我的 SQL-fu 不够强大，无法根据我的情况调整这些。我对该站点上使用 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;
Can anyone give me a hand with this? Any help would be greatly appreciated!
任何人都可以帮我解决这个问题吗？任何帮助将不胜感激！
采纳答案by Adriaan Stander
Have a look at this
看看这个
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
回答by Digital Mindspring
Turns out there is an even easier way to do this which doesn't require a UDF:
事实证明，有一种更简单的方法可以做到这一点，它不需要 UDF：
select replace(replace(replace((cast((
select distinct columnName as X
from tableName
for xml path('')) as varchar(max))),
'</X><X>', ', '),'<X>', ''),'</X>','')
回答by Talha
Had similar problem and found a sweet solution after playing with code for 15 minutes
遇到了类似的问题，玩了 15 分钟的代码后找到了一个甜蜜的解决方案
declare @result varchar(1000)
select @result = COALESCE(@result+','+A.col1, A.col1)
FROM ( select col1
from [table]
) A
select @result
Returns result as value1,value2,value3,value4
返回结果为 value1,value2,value3,value4
Enjoy ;)
享受 ;）
回答by John
SqlServer 2017 now has STRING_AGGthat aggregates multiple strings into one using a given separator.
SqlServer 2017 现在具有STRING_AGG，它使用给定的分隔符将多个字符串聚合为一个。
回答by TimG
There are 3 ways I have dealt with rolling-up data, as you have described, 1.use a cursor, 2.use a UDF or 3. use the a Custom Aggregate (written in .NET CLR).
The Cursor and UDF are pretty slow. (approx 0.1 sec per row). The CLR custom aggregate is surprisingly fast. (approx 0.001 sec per row)
正如您所描述的，我有 3 种处理汇总数据的方法，1. 使用游标，2. 使用 UDF 或 3. 使用自定义聚合（用 .NET CLR 编写）。
Cursor 和 UDF 非常慢。（每行约 0.1 秒）。CLR 自定义聚合的速度惊人地快。（每行约 0.001 秒）
Microsoft ships the code (to do exactly what you want) as part of the SDK for SQL 2005. If you have it installed, you should be able to find the code in this folder: C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities. You might also want to this article in MSDN. It talks about installing the custom aggregate and enabling it: http://msdn.microsoft.com/en-us/library/ms161551(SQL.90).aspx
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
Once you compile and install the custom aggregate, you should be able to query like this:
编译并安装自定义聚合后，您应该能够像这样查询：
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;
and get a result set like you showed (above)
并获得您显示的结果集（上图）
回答by Wael Ali
Now from SQL server 2017 there is a new T-SQLfunction called STRING_AGG:
it is a new aggregate function that concatenates the values of string expressions and places separator values between them.
The separator is not added at the end of string.
现在从 SQL Server 2017 开始，有一个名为的新T-SQL函数STRING_AGG：
它是一个新的聚合函数，用于连接字符串表达式的值并在它们之间放置分隔符值。
字符串末尾不添加分隔符。
Example:
例子：
SELECT STRING_AGG ( ISNULL(FirstName,'N/A'), ',') AS csv
FROM Person.Person;
the result set:
结果集：
John,N/A,Mike,Peter,N/A,N/A,Alice,Bob
回答by pradeep
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
Result
结果
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,
回答by Charles Bretana
Try this:
尝试这个：
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
回答by JeffO
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;
回答by Paul Sasik
A UDFwould be an ok way to solve this.
一个UDF将是解决这个的确定方式。
Just define a T-SQL function (UDF) that takes an int param (product ID) and returns a string (concatenation of names associated with the product.) If your method's name is GetProductNames then your query might look like this:
只需定义一个 T-SQL 函数 (UDF)，它接受一个 int 参数（产品 ID）并返回一个字符串（与产品关联的名称的串联）。如果您的方法的名称是 GetProductNames，那么您的查询可能如下所示：
SELECT p1.CategoryId, dbo.GetProductNames(p1.CategoryId)
FROM Northwind.dbo.Products p1
GROUP BY CategoryId
