How to Transpose SQL Rows into Columns Using PostgreSQL?

Transposing SQL Rows to Columns: A Comprehensive Guide

In SQL, data can often be structured with rows representing individual records and columns containing specific attributes. However, there are situations where it becomes necessary to transform rows into columns, a process known as transposition. In this article, we'll explore a solution for transposing rows as columns using PostgreSQL.

The Problem

Consider the following scenario: you have two tables, one with responses to questions and the other containing the actual questions. The goal is to transpose the data so that each user has a row representing their responses to all questions.

responses:
user_id | question_id | body
----------------------------
1       | 1           | Yes
2       | 1           | Yes
1       | 2           | Yes
2       | 2           | No
1       | 3           | No
2       | 3           | No

questions:
id | body
-------------------------
1 | Do you like apples?
2 | Do you like oranges?
3 | Do you like carrots?

The desired output would look like this:

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

The Solution

To transpose the data, we'll employ a combination of JOIN and MAX() with a CASE expression. The query is as follows:

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

Breaking Down the Query

1. JOIN: The JOIN operation establishes a relationship between the RESPONSES and QUESTIONS tables based on the common question_id column. This allows us to pair responses with their corresponding questions.

2. MAX() with CASE: The MAX() function is used to aggregate the body column, considering only the maximum value for each user and question combination. The CASE expression within MAX() acts as a conditional statement that retrieves the body value for a specific question or NULL if the condition is not met.

3. GROUP BY: The GROUP BY clause groups the results by the user_id column. This ensures that each row in the output represents a unique user.

Conclusion

The provided SQL query effectively transposes rows into columns, allowing you to easily extract user responses for a set of questions. This technique is commonly referred to as pivoting data and is often used in data analysis and reporting. By utilizing the JOIN, MAX(), and CASE constructs alongside GROUP BY, you can achieve powerful data transformations in SQL.

The above is the detailed content of How to Transpose SQL Rows into Columns Using PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!