How to Efficiently Convert Rows to Columns in PostgreSQL?

Last Updated : 17 Oct, 2024

Converting rows to columns, often referred to as pivoting or transposing, is a crucial aspect of data transformation in SQL. This technique is useful for improving data readability, facilitating analysis, aligning data formats with the requirements of reporting tools, and optimizing queries. In PostgreSQL, we can achieve this using methods like the CASE statement and the CROSSTAB function from the tablefunc extension.

This article will walk you through how to manipulate and convert rows to columns in PostgreSQL, with practical examples. We’ll cover two approaches: using CASE statements and the CROSSTAB function to convert rows to columns, along with the necessary steps to implement them efficiently.

How to Convert Columns to Rows in PostgreSQL

Before we begin, it’s essential to understand the structure of our data. Identify the columns that need to be pivoted and the unique identifiers that link rows together. In PostgreSQL, there are two popular methods to perform this transformation:

Using CASE Statements
Using CROSSTAB function

Method 1: Using CASE Statements

One easy way to convert rows to columns in PostgreSQL is by using the CASE statement along with aggregation functions(MAX, MIN, SUM, etc...). This approach is suitable when we have a limited number of distinct values in the column we want to transpose. Here a table needs one unique key column to apply group-by clause.

Syntax:

SELECT
    id,
    AGGR_FUNC(CASE WHEN column_name = 'value1' THEN result_column END) AS new_column1,
    AGGR_FUNC(CASE WHEN column_name = 'value2' THEN result_column END) AS new_column2
FROM
    table_name
GROUP BY
    id;

key terms

AGGR_FUNC: An aggregate function like SUM(), MIN(), or MAX().
id: The unique identifier for rows in the table.
column_name: The column whose values will determine the new columns.

Example: Converting Rows to Columns Using CASE Statements

The following example will show how the CASE statement can be used to manually pivot data, allowing us to display employee names based on their department in separate columns. To convert the distinct dept values (Sales and Accounting) into columns, we can write the following query using CASE statement.

Step 1: Create the Employees Table

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');

Output

empId	name	dept
0001	Clark	Sales
0002	Dave	Accounting
0003	Ava	Sales

Step 2: Convert Rows to Columns Using the CASE Statement

To pivot the distinct dept values (Sales and Accounting) into separate columns, we can write the following query using the CASE statement:

Query:

SELECT
    empid,
    MAX(CASE WHEN dept = 'Sales' THEN name END) AS Sales,
    MAX(CASE WHEN dept = 'Accounting' THEN name END) AS Accounting
FROM
    Employee
GROUP BY
    empid;

Output

using-case-statemnet — Using Case statement

Explanation:

The two distinct values in the dept column (Sales and Accounting) are pivoted into columns.
For each empid, we use CASE to conditionally display the employee names in their respective department columns.
For example, Clark appears in the Sales column for empid 0001, while Dave appears in the Accounting column for empid 0002.

This approach is suitable when you have a small and manageable number of distinct values to pivot. However, if we are dealing with a dynamic set of values, the CROSSTAB function is a more efficient solution.

Method 2: Using the CROSSTAB Function

PostgreSQL provides the crosstab function, which is part of the tablefunc extension. This function simplifies the process of transposing data when dealing with unknown values or dynamic values by automatically converting rows into columns. Ensure that the tablefunc extension is installed and enabled before using it.

Step 1: Install the tablefunc Extension

Before using the CROSSTAB function, we need to install the tablefunc extension. Run the following command to enable it:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Syntax:

SELECT * FROM crosstab(
    'SELECT id, category, value FROM your_table ORDER BY 1, 2',
    'SELECT DISTINCT category FROM your_table ORDER BY 1'
) AS ct(id type, column1 type, column2 type, ...);

key terms

crosstab: The function to pivot the table.
The first argument is a query that retrieves the data to be pivoted.
The second argument provides the list of unique values (to be turned into columns).

Example: Converting Rows to Columns Using CROSSTAB Function

In this example, the CROSSTAB function is used to dynamically convert the dept values into columns. We will use the same EMPLOYEE table to demonstrate how to convert rows to columns using the CROSSTAB function. The first query retrieves the empid, dept, and name values, and the second query fetches the distinct departments (Sales and Accounting).

Query:

SELECT * FROM crosstab(
   'SELECT empid, dept, name FROM Employee ORDER BY 1,2',
   'SELECT DISTINCT dept FROM Employee ORDER BY 1'
) AS ct(empid int, Sales VARCHAR(50), Accounting VARCHAR(50));

Output

Using-CROSSTAB-function — Using CROSSTAB function

Explanation:

The crosstab function dynamically transforms the data based on the distinct values in the dept column.
It automatically creates separate columns for each department and fills them with the appropriate employee names.

Ensure that the source query (SELECT empid, dept, name FROM Employee ORDER BY 1, 2) is correctly ordered, as the CROSSTAB function relies on the proper order to work.

Conclusion

In PostgreSQL, transposing rows to columns can be efficiently acheived using either CASE statements or the CROSSTAB function. The CASE statement approach is ideal for smaller datasets with a limited number of distinct values, while the CROSSTAB function is better suited for more dynamic and complex tasks. Both methods are invaluable for data transformation, reporting, and analysis in PostgreSQL.

How to Efficiently Convert Rows to Columns in MariaDB?

nikhilkalburgi

Improve

Article Tags :

How to Efficiently Convert Rows to Columns in PostgreSQL?

How to Convert Columns to Rows in PostgreSQL

Method 1: Using CASE Statements

Example: Converting Rows to Columns Using CASE Statements

Step 1: Create the Employees Table

Step 2: Convert Rows to Columns Using the CASE Statement

Method 2: Using the CROSSTAB Function

Step 1: Install the tablefunc Extension

Example: Converting Rows to Columns Using CROSSTAB Function

Conclusion

Similar Reads