连接行值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屋！