连接行值T-SQL [英] Concatenate row values T-SQL
问题描述
我正在尝试为报告汇总一些数据,并且需要连接表之一的行值。这是基本的表格结构:
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:
评论
ReviewID
ReviewDate
审阅者
ReviewerID
ReviewID
UserID
用户
UserID
FName
LName
这是M:M关系。每个评论可以有许多评论者;每个用户可以与许多评论相关联。
This is a M:M relationship. Each Review can have many Reviewers; each User can be associated with many Reviews.
基本上,我只想看到的是Reviews.ReviewID,Reviews.ReviewDate和所有FName的串联字符串。与此评论相关的用户(以逗号分隔)。
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).
而不是:
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-fu不够强大,无法适应我的情况。我对该站点上使用FOR XML PATH()的示例特别感兴趣,因为它看起来最干净,最直接。
I have found this article 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.
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;
有人可以帮我吗?
推荐答案
看看这个
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
这篇关于连接行值T-SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!