How to Efficiently Convert Rows to Columns in PostgreSQL?How to Convert Columns to Rows 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:
- Using CASE Statements
- 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:
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:
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.