sql - 如何以特定格式显示sql查询

Question

我是 SQL 新手。我试图让我的查询以下面的格式显示:

FacultyName          Book     Book chapter   Journal Article     Conference
CLM                   5            11              5                 1
Health Sciences       1            0               0                 0
Humanities            1            0               0                 0
Science               0            0               0                 3

目前，它是这样显示的:

FacultyName              Type            CountOfSubmissionID
CLM                      Book                     5
CLM                      Book chapter             11
CLM                      Conference               1
CLM                      Journal article          5
Health Sciences          Book                     1
Humanities               Book                     1
Science                  Conference               3

这是让我得到这个的查询:

SELECT Faculty.FacultyName,
       Submission.Type,
       Count(Submission.SubmissionID) AS CountOfSubmissionID
FROM Submission
  INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[Faculty ID])
    INNER JOIN (Researcher INNER JOIN ResearcherSubmission
                  ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID)
        ON School.SchoolID = Researcher.SchoolID)
      ON Submission.SubmissionID = ResearcherSubmission.SubmissionID
GROUP BY Faculty.FacultyName, Submission.Type

这是我的表格:

请告知如何让我的查询以我绘制的第一个表格的格式显示。

Answer 1

您可以使用 CASE。在您的代码中试试这句话:

 SELECT Faculty.FacultyName, 
        SUM( CASE WHEN Submission.Type='Book' THEN 1 ELSE 0 END) Book,
        SUM( CASE WHEN Submission.Type='Book Chapter' THEN 1 ELSE 0 END) BookChapter,
        SUM( CASE WHEN Submission.Type='Conference' THEN 1 ELSE 0 END) Conference
 FROM Submission INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[Faculty ID]) INNER JOIN (Researcher INNER JOIN ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID) ON School.SchoolID = Researcher.SchoolID) ON Submission.SubmissionID = ResearcherSubmission.SubmissionID
 GROUP BY Faculty.FacultyName, Submission.Type;

祝你好运!