How Can I Transpose Rows into Columns in SQL Using a Pivot Query?

SQL Transpose Rows as Columns: A Pivoted Solution

In SQL, transposing rows as columns is a useful technique to reshape tabular data. Given a table with rows representing data points and columns representing variables, you may encounter scenarios where it's necessary to switch the orientation of the data.

One such scenario, as described in the question, involves tables with responses and questions. The goal is to transpose the response data into a columnar format, where each question becomes a column and each user's response to that question is displayed in the corresponding row.

To achieve this transposition, you can utilize a technique known as a "pivot query." The following SQL statement demonstrates a solution using PostgresSQL:

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

This query uses conditional aggregation to group and aggregate the response values based on the question ID. The CASE statements extract the responses for each question and assign them to specific columns. The MAX() function is used to ensure that only the latest response for each question-user pair is included.

The output of this query will resemble the desired transposed format, where each user's ID is listed in the first column, followed by a column for each question that contains the user's response (or NULL if no response exists).

This pivot query is a standard approach for transposing rows as columns in SQL. It provides an efficient and flexible solution for dynamically handling an arbitrary number of questions and response combinations.

The above is the detailed content of How Can I Transpose Rows into Columns in SQL Using a Pivot Query?. For more information, please follow other related articles on the PHP Chinese website!