时间戳

首页 > 解决方案 > SQL：一2多行，多行合并为一列

sql - SQL：一2多行，多行合并为一列

问题描述

我有四张表，分别tblProject是（一条记录）、tblTeamMembers（多条记录）、tblProjectStatus（查找表）和tblProjectScoresComments（多条记录）。我正在使用 SQL Server 2017。

以下是表格定义：

tbl项目：

[ProjectID] [INT] IDENTITY(1,1) NOT NULL,
[ProjectName] [NVARCHAR](150) NOT NULL,
[CommunityProblem] [NTEXT] NOT NULL,
[IctSolveCommunityProblem] [NTEXT] NOT NULL,
[TeamMemberRoles] [NTEXT] NOT NULL,
[ProjectImpact] [NTEXT] NOT NULL,
[HelpRaiseFunds] [NTEXT] NOT NULL,
[ProjectStatus] [INT] NOT NULL,
[CaptureDate] [DATE] NOT NULL

tbl团队成员：

[MemberID] [INT] IDENTITY(1,1) NOT NULL,
[Person] [NVARCHAR](150) NOT NULL,
[SalRef] [NVARCHAR](50) NOT NULL,
[Email] [NVARCHAR](150) NOT NULL,
[UserName] [NVARCHAR](150) NOT NULL,
[TeamLeader] [INT] NOT NULL,
[ProjectLeader] [INT] NOT NULL,
[ProjectLeaderContactNo] [NVARCHAR](150) NULL,
[ProjectID] [INT] NOT NULL

tblProjectScores评论

[RecID] [INT] IDENTITY(1,1) NOT NULL,
[ProjectID] [INT] NOT NULL,
[Score] [FLOAT] NOT NULL,
[Comments] [NVARCHAR](MAX) NULL,
[UserID] [NVARCHAR](150) NOT NULL,
[DateCaptured] [DATETIME] NOT NULL

tbl项目状态：

[ProjectStatusID] [INT] IDENTITY(1,1) NOT NULL,
[ProjectStatus] [NVARCHAR](100) NOT NULL

我希望结果返回所有三个表中的列，但是第三个表 ( tblProjectScoresComments) 有很多记录，并且对于该列[Score]应该返回一个平均值，并且该[Comments]列应该将所有评论作为 1 列返回，并且每个评论应该由逗号 ( ,)。

我想使用类似于以下的查询：

SELECT 
    p.ProjectID, p.ProjectName AS Project, 
    ps.ProjectStatus, 
    tm.Person AS ProjectLeader, 
    p.CaptureDate, [AVERAGE_SCORE_FOR_ALL] AS Score,  
    [ALL_COMMENTS_MERGED_TO_ONE_COLUMN] AS Comments
FROM
    dbo.tblProject AS p 
INNER JOIN 
    dbo.tblProjectStatus AS ps ON p.ProjectStatus = ps.ProjectStatusID 
INNER JOIN 
    dbo.tblTeamMembers AS tm ON p.ProjectID = tm.ProjectID 
INNER JOIN 
    dbo.tblProjectScoresComments AS psc ON p.ProjectID = psc.ProjectID
WHERE
    (tm.ProjectLeader = 1)

结果应如下所示：

ProjectID | Project                                                   | ProjectStatus    | ProjectLeader | CaptureDate | Score   |Comments
---------------------------------------------------------------------------------------------------------------------------------------------------------------
1         | Access to ICT-Makatane High School and Community Project  | Not yet decided  | Mary Ndlovu   | 2019-10-04  | 1.67    |Comment 1,Comment 2,Comment 3
2         | Asample project                                           | Rejected         | Joe Soap      | 2019-11-07  | 3       |Comment 1,Comment 2

非常感谢帮助！

标签： sqlsql-serverone-to-many

解决方案

这听起来像聚合：

SELECT p.ProjectID, p.ProjectName AS Project, ps.ProjectStatus, tm.Person AS ProjectLeader, 
p.CaptureDate,
       avg(psc.score) AS avg_score, 
       string_agg(psc.comments, ' ') as Comments
FROM dbo.tblProject p JOIN
     dbo.tblProjectStatus ps
     ON p.ProjectStatus = ps.ProjectStatusID JOIN
     dbo.tblTeamMembers tm
     ON p.ProjectID = tm.ProjectID JOIN
     dbo.tblProjectScoresComments 
     psc
     ON p.ProjectID = psc.ProjectID
WHERE tm.ProjectLeader = 1
GROUP BY p.ProjectID, p.ProjectName AS Project, ps.ProjectStatus, tm.Person AS ProjectLeader, 
p.CaptureDate;

编辑：

的替代方案string_agg()相当混乱：

SELECT . . .,
       psc.avg_score, c.comments,
FROM dbo.tblProject p JOIN
     dbo.tblProjectStatus ps
     ON p.ProjectStatus = ps.ProjectStatusID JOIN
     dbo.tblTeamMembers tm
     ON p.ProjectID = tm.ProjectID OUTER APPLY
     (SELECT STUFF( (SELECT ', ' + psc.comment
                     FROM dbo.tblProjectScoresComments psc
                     WHERE p.ProjectID = psc.ProjectID
                     FOR XML PATH (''), TYPE 
                    ).value(N'.[1]', N'nvarchar(max)'
                           ), 1, 2, ''
                  ) as comments
     ) c OUTER APPLY
     (SELECT AVG(psc.score) as avg_score
      FROM dbo.tblProjectScoresComments psc
      WHERE p.ProjectID = psc.ProjectID
     ) psc

外部GROUP BY不应该是必要的。

推荐阅读