How to Efficiently Convert Rows to Columns in PostgreSQL?

In short, converting rows to columns in a table can be done for improved readability, better analysis, alignment with tool requirements, and optimization of certain queries. It is a data manipulation technique used to enhance data presentation, facilitate specific analyses, and meet the formatting requirements of reporting tools or applications.

This article will explain how to manipulate and translate rows to columns in PostgreSQL. This process called pivoting or transposing which is a crucial aspect of data transformation. This is essential as it can be used in analyzing the table for different unique values of columns.

How to Convert Columns to Rows in PostgreSQL

Firstly, we need to have a clear understanding of data structure by identifying the columns you want to pivot and the unique identifiers that link the rows together. Then, convert the rows to columns. Here, we have 2 methods to do so:

1. Using CASE Statements
2. Using CROSSTAB function

Example: Let us create an EMPLOYEE table with three records as follows:

-- Creating the table above
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');

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

We will use the above table to implement the approach in PostgreSQL. You can execute it in Postgres Admin or any online Postgres compiler.

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 you have a limited number of distinct values in the column you want to transpose. Here a table needs one unique key column to apply group-by-clause.

Syntax:

SELECT
    id,
    AGGR_FUNC(CASE WHEN category = 'A' THEN value END) AS A,
    AGGR_FUNC(CASE WHEN category = 'B' THEN value END) AS B,
    AGGR_FUNC(CASE WHEN category = 'C' THEN value END) AS C,
    ...
FROM
    <your_table>
GROUP BY
    id;

where,

• AGGR_FUNC: The aggregate function like SUM, MIN, MAX etc…
• …: Can have unlimited case statements
• id: the unique identifier row in the table

Convert Rows to Columns:

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:

2 Rows values (sales, accounting) to 2 Columns

Explanation: The two distinct values in rows sales and accounting are pivoted to columns and the 3 groups are formed for 3 empids. For every group, This article will explain the Max of names selected for every dept.

Using 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. Ensure that the tablefunc extension is installed and enabled before using it.

Syntax:

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    'SELECT id, category,... FROM <your_table> ORDER BY 1,2,...',
    'SELECT DISTINCT category FROM <your_table> ORDER BY 1'
) AS ct(id <type>, column_1 <type>, column_2 <type>, column_3 <type>, ...);

where,

• crosstab: the function to pivot table. The first argument to crosstab is the source query that provides the data for the crosstabulation. The second argument is a query that produces the set of column names.
• ct: Alias with column names

Example: Here, we will use the EMPLOYEE table to convert its rows to columns and create columns for all distinct departments

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:

Same results with CROSSTAB Function

Explanation: Here the crosstab function dynamically pivots the data by creating separate columns for each distinct department. Ensure the source query (‘SELECT empid, dept, name FROM Employee ORDER BY 1,2‘) includes an ORDER BY clause to ensure proper ordering.

Conclusion

We learned to efficiently convert rows to columns in PostgreSQL using the CASE and CROSSTAB functions from the tablefunc extension. CASE Statement made you add multiple lines of code and in 2nd method, you installed the extension, structured your source query with an ORDER BY clause for proper ordering, and used crosstab with a dynamic query for column names. This method simplified the transformation by providing a concise and powerful way to pivot data for analysis in PostgreSQL.