J'ai quatre tables, à savoir tblProject (un enregistrement), tblTeamMembers (plusieurs enregistrements), tblProjectStatus (table de recherche) et tblProjectScoresComments (plusieurs enregistrements). J'utilise SQL Server 2017.

Voici les définitions des tableaux :

tblProjet :

[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

membres de l'équipe 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

tblProjectScoresCommentaires

[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

tblProjectStatus :

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

J'aimerais que les résultats renvoient les colonnes des trois tables, mais la 3ème table (tblProjectScoresComments) a de nombreux enregistrements et pour la colonne [Score] une moyenne doit être renvoyée, et la colonne [Comments] devrait avoir tous les commentaires renvoyés sur 1 colonne et chaque commentaire devrait être séparé par une virgule (,).

Je voudrais utiliser une requête similaire à celle ci-dessous :

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)

Les résultats devraient ressembler à ceci :

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

J'apprécierais vraiment de l'aide !

1
Johan v Dk 8 févr. 2020 à 15:42

1 réponse

Meilleure réponse

Cela ressemble à une agrégation:

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;

ÉDITER:

L'alternative à string_agg() est plutôt désordonnée :

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 

Un GROUP BY externe ne devrait pas être nécessaire.

0
Gordon Linoff 8 févr. 2020 à 18:28