Convert oracle pivot to Postgres query

Question

I have a query in Oracle that creates a pivot table and I'm trying to convert it over to postgres.

In Oracle I had the following:

CREATE OR REPLACE VIEW PROGRAM_EXPENDITURES AS (
SELECT ROWNUM AS ID, a.*
FROM (
SELECT * FROM (
    SELECT 8400000 AS PROGRAM_TOTAL, PROGRAM_INCOME, INCENTIVE_AMT_NUM, APP_STATUS FROM HB357
    )
PIVOT (
    SUM(INCENTIVE_AMT_NUM) as incentive, sum(program_income) as income for app_status in ('approved' approved, 'pending' pending, 'void' void)
    )

)a)

Which gives an output of:

ID | PROGRAM_TOTAL | APPROVED_INCENTIVE | PENDING_INCENTIVE | VOID_INCENTIVE | APPROVED_INCOME | PENDING_INCOME | VOID_INCOME
------------------------------------------------------------------------------------------------------------------------------
1   84000000         3386600               2108.75            1500             2000000           5000000             100

I read this post about using CASE STATEMENTS in PG and have done the following:

 SELECT 
    8400000 AS PROGRAM_TOTAL,
    CASE WHEN hb357.app_status = 'Approved' THEN SUM(hb357.incentive_amt_num) END as approved_incentive,
    CASE WHEN hb357.app_status = 'Pending' THEN SUM(hb357.incentive_amt_num) END as pending_incentive,
    CASE WHEN hb357.app_status = 'Void' THEN SUM(hb357.incentive_amt_num) END as void_incentive,
    CASE WHEN hb357.app_status = 'Approved' THEN SUM(hb357.program_income) END as approved_income,
    CASE WHEN hb357.app_status = 'Pending' THEN SUM(hb357.program_income) END as pending_income,
    CASE WHEN hb357.app_status = 'Void' THEN SUM(hb357.program_income) END as void_income
FROM WOODBURN.HB357
GROUP BY hb357.app_status

But this splits the results by app status into separate rows because of the GROUP BY statement.

But I need the all on the same row. So that results look like this:

How can achieve this in PG?

@a_horse_with_no_name : can't remove the `GROUP BY hb357.app_status` clause because of the aggregate functions in the `SELECT` and hb357.app_status is not included in these aggregate functions. — Edouard, Dec 07 '21 at 08:00

Edouard · Answer 1 · 2021-12-07T16:47:10.140

Assuming that you want to get one resulting row per group, each group identified by group_id :

 SELECT DISTINCT ON (group_id)
        group_id,
        SUM(hb357.incentive_amt_num + hb357.program_income) OVER w AS PROGRAM_TOTAL,
        SUM(hb357.incentive_amt_num) FILTER (WHERE hb357.app_status = 'Approved') OVER w as approved_incentive,
        SUM(hb357.incentive_amt_num) FILTER (WHERE hb357.app_status = 'Pending') OVER w as pending_incentive,
        SUM(hb357.incentive_amt_num) FILTER (WHERE hb357.app_status = 'Void') OVER w as void_incentive,
        SUM(hb357.program_income) FILTER (WHERE hb357.app_status = 'Approved') OVER w as approved_income,
        SUM(hb357.program_income) FILTER (WHERE hb357.app_status = 'Pending') OVER w as pending_income,
        SUM(hb357.program_income) FILTER (WHERE hb357.app_status = 'Void') OVER w as void_income
    FROM WOODBURN.HB357
  WINDOW w AS (PARTITION BY group_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Thank you for the suggestion, but doing that would only give back results of one of the groups. I've added more info to the OP for clarity. — reactFullStackDeveloper, Dec 07 '21 at 15:44
Your query shows only one expexted row (corresponding to one "group" ?). I've updated the answer to that to get one resulting row per group, each group is identified by the `group_id` column. — Edouard, Dec 07 '21 at 16:49

Convert oracle pivot to Postgres query

1 Answers1