Implement Pivot function in SQL

Pivoting data is a common task in reporting. In this article, we will talk about using SQL statement to implement simple pivoting function.

Here is a scenario: We have a survey. For each question, use can choose from following answers:

1. Strongly Agree,
2. Agree
3. Disagree
4. Strongly Disagree
5. Not Applicable

The table to store user's answers is:

CREATE TABLE SurveyAnswer
(
    EmployeeID int NOT NULL,
    QuestionID int NOT NULL,
    AnswerID int NULL
)

The AnswerID is 1 to 5 corresponding to the answer list above.

We would like to display the survey summary as following:

Question | Strongly Agree | Agree | Disagree | Strongly Disagree | Not Applicable

The SQL to get the results is:

SELECT
    QustionID,
    ISNULL(SUM(CASE WHEN AnswerID = 1 THEN AnswerCount END), 0) AS StronlyAgreeCount,
    ISNULL(SUM(CASE WHEN AnswerID = 2 THEN AnswerCount END), 0) AS AgreeCount,
    ISNULL(SUM(CASE WHEN AnswerID = 3 THEN AnswerCount END), 0) AS DisagreeCount,,
    ISNULL(SUM(CASE WHEN AnswerID = 4 THEN AnswerCount END), 0) AS StronlyDisagreeCount,,
    ISNULL(SUM(CASE WHEN AnswerID = 5 THEN AnswerCount END), 0) AS NACount
FROM
(
    SELECT QuestionID, AnswerID, count(*) AS AnswerCount
    FROM SurveyAnswer
    GROUP BY QuestionID, AnswerID
) x