In PostgreSQL, the PIVOT is used to transform rows into columns, which is particularly useful for generating summary reports. Although PostgreSQL does not have a built-inPIVOTfunction like some other SQL databases, you can achieve similar results using the crosstab function provided by the tablefunc extension. This operation is often used in reporting to aggregate data and present it in a more readable format.

Steps to Implement a Pivot Table in PostgreSQL

1. Install the tablefunc Extension
   • Before you can use the crosstab function, you need to install the tablefunc extension in your PostgreSQL database.

     CREATE EXTENSION tablefunc;
       

2. Create a Sample Table
   • Assume you have atransactions table with columns for transaction_date, account_id, and amount.

   CREATE TABLE transactions (
     transaction_date DATE,
     account_id INT,
     amount DECIMAL
   );
   

3. Insert Sample Data
   • Populate the transactionstable with some sample data.

   INSERT INTO transactions (transaction_date, account_id, amount) VALUES
   ('2024-01-01', 101, 150.00),
   ('2024-01-01', 102, 200.00),
   ('2024-02-01', 101, 180.00),
   ('2024-02-01', 102, 220.00);
   

4. Create a Pivot Query
   • Use the crosstabfunction to create a pivot table that shows total transaction amounts by account and month.

   SELECT * FROM crosstab(
     'SELECT
        account_id,
        to_char(transaction_date, ''YYYY-MM'') AS month,
        SUM(amount) AS total_amount
      FROM transactions
      GROUP BY account_id, month
      ORDER BY account_id, month'
   ) AS ct (
     account_id INT,
     "2024-01" DECIMAL,
     "2024-02" DECIMAL
   );
   

5. Understand the Pivot Output
   • The output of the above query will be a table where account_id is in the rows, and each month has its own column showing the total amount for that month.
6. Handling Dynamic Columns
   • If you need dynamic columns (e.g., months for different years), you would need to generate SQL dynamically using programming logic or additional SQL queries.

Key Points

• Extension Requirement:crosstab requires the tablefunc extension.
• Static Columns: The crosstab function needs predefined column names.
• Dynamic Columns: Handling dynamic columns requires additional logic.

This approach provides a flexible way to pivot data in PostgreSQL and generate reports that are easy to interpret.

To gain complete access, login with gmail or outlook, no need of signup. click here

SQL PIVOT TECHNIQUE

Let's delve into the specifics of implementing the pivot technique across different SQL databases like MySQL, PostgreSQL, Oracle, and Microsoft SQL Server. In our example, we aim to utilize the SQL pivot method to organize daily order counts, distinguishing between orders placed by couples and those by bachelors.

Raw data from order table

Step1, Get client's marital status

Utilize below query to merge the 'Orders' table with the 'Clients' table, thereby obtaining the marital status of clients from the 'Clients' table. Additionally, transform the 'OrderDateTime' into just the 'OrderDate'.

SELECT
    a.order_number, DATE(a.order_date), a.client_id,
    CASE WHEN b.married_flag = 1 THEN 'Married'
ELSE 'Single' END AS marital_status
FROM act_order a
INNER JOIN org_client b on b.client_id = a.client_id
order by order_id;

Step 1 output

MySQL and PostGreSQL PIVOT technique

SELECT
    DATE(a.order_date) as order_date,
    SUM(CASE WHEN b.married_flag = 1 THEN 1 END) AS couples_orders,
    SUM(CASE WHEN b.married_flag = 0 THEN 1 END) AS bachelors_orders
FROM act_order a
INNER JOIN org_client b on b.client_id = a.client_id
GROUP BY DATE(a.order_date);

MS SQL SERVER PIVOT technique

SELECT * FROM
(SELECT  CONVERT(DATE, a.order_date) as order_date , a.client_id
    CASE WHEN b.married_flag = 1 THEN 'Married'
ELSE 'Single' END AS marital_status
FROM act_order a
INNER JOIN org_client b on b.client_id = a.client_id
) t
PIVOT(
 COUNT(client_id)
FOR marital_status IN (
[Married],
[Single])
) AS pivot_table;

Oracle PIVOT technique

SELECT * FROM
(SELECT  TO_CHAR(a.order_date,'DD/MM/YY') as order_date , a.client_id
    CASE WHEN b.married_flag = 1 THEN 'Married'
ELSE 'Single' END AS marital_status
FROM act_order a
INNER JOIN org_client b on b.client_id = a.client_id
) t
PIVOT(
COUNT(client_id)
FOR marital_status IN ('Married','Single')
)

FINAL OUTPUT