CODEWITHSUNDEEP
sqlsql-server
Kivan Ilangakoon
Kivan Ilangakoon

Reputation: 457

How to display sql query in a specific format

I am new to SQL. I am trying to get my query to display in the format below:

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

At the moment, this is how it is displayed:

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

This the query that got me this:

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

Here are my tables:

Table Structure

Please advice on how I can get my query to display in the format of the first table I have drawn up.

Upvotes: 3

Views: 325

Answers (1)

David Isla
David Isla

Reputation: 629

You can use CASE. Try this sentence in your code:

 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;

Good Luck!

Upvotes: 4

Related Questions