How to use CASE Statements In SQL
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.
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.