How to Pivot Rows into Columns in SQL for User Responses?

SQL Transpose Rows as Columns

Transposing rows to columns in SQL is a technique that allows you to convert a table with row-oriented data into a table with column-oriented data. This technique is commonly known as pivoting.

In your specific scenario, you have two tables:

• responses: Contains the user responses to questions.
• questions: Contains the actual questions.

Your goal is to transpose the "responses" table so that each user's responses to different questions appear as columns, rather than rows.

Step-by-Step Solution:

To achieve the desired transposition, you can use a combination of joins and conditional aggregation:

SELECT r.user_id,
       MAX(CASE WHEN r.question_id = 1 THEN r.body ELSE NULL END) AS "Do you like apples?",
       MAX(CASE WHEN r.question_id = 2 THEN r.body ELSE NULL END) AS "Do you like oranges?",
       MAX(CASE WHEN r.question_id = 3 THEN r.body ELSE NULL END) AS "Do you like carrots?"
FROM responses r
JOIN questions q ON q.id = r.question_id
GROUP BY r.user_id;

Explanation:

• The query first joins the "responses" and "questions" tables on the "question_id" column, which links each response to its corresponding question.
• It then uses conditional aggregation with the MAX() function to retrieve the maximum response value for each user and question combination.
• The CASE statement checks the question ID and returns the response body if it matches the ID.
• Finally, the GROUP BY clause groups the results by user ID, ensuring that each user's responses are aggregated together.

Example Result:

user_id | Do you like apples? | Do you like oranges? | Do you like carrots?
-------- | ------------------ | ------------------- | -------------------
1       | Yes                 | Yes                  | No
2       | Yes                 | No                   | No

The above is the detailed content of How to Pivot Rows into Columns in SQL for User Responses?. For more information, please follow other related articles on the PHP Chinese website!