Create a Pivot Table With Postgresql

Create a pivot table with PostgreSQL

First compute the average with the aggregate function avg():

SELECT neighborhood, bedrooms, avg(price)
FROM   listings
GROUP  BY 1,2
ORDER  BY 1,2;

Then feed the result to the crosstab() function as instructed in great detail in this related answer:

• PostgreSQL Crosstab Query

How to create a PostgreSQL pivot table that pivots multiple columns?

You can try using conditonal aggregation

select system,MICROSERVICE , MONTH,
max(case when METRIC='uptime' then VALUE end) as uptime_value,
max(case when METRIC='uptime' then CONFIDENCE_LEVEL end) as uptime_confidence,
max(case when METRIC='lag' then VALUE end) as lag_value,
max(case when METRIC='lag' then CONFIDENCE_LEVEL end) as lag_confidence
from tablename
group by system,MICROSERVICE , MONTH

how to create pivot table in postgresql

You can simplify this to a single statement using conditional aggregation.

However, this part of your derived tables:

array_agg(distinct plan_type) as direct_plan
...
where plan_type= 'direct' 

seems wrong because the aggregated column and the filter column are identical. I assume you actually meant to aggregate the plan_type column and filter on the invoice_type column (at least after looking at the screen shots).

So the simplified statement would look like this:

SELECT im.user_id,
       array_agg(distinct im.plan_type) filter (where im.invoice_type = 'direct') as direct_plan,
       array_agg(distinct im.plan_type) filter (where im.invoice_type = 'promotion') as promotion_plan,
       array_agg(distinct im.plan_type) filter (where im.invoice_type = 'giftcode') as giftcode_plan,
       count(*) filter (where invoice_type = 'direct') as count_direct,
       count(*) filter (where invoice_type = 'promotion') as count_promotion,
       count(*) filter (where invoice_type = 'giftcode') as count_giftcode,
FROM payment.invoices_map im
group by user_id

Pivot table using crosstab and count

1. Static solution with a limited list of marking values :

SELECT year
     , TO_CHAR( creation_date, 'Month') AS month
     , COUNT(*) FILTER (WHERE marking = 'Delivered') AS Delivered
     , COUNT(*) FILTER (WHERE marking = 'Not delivered') AS "Not delivered"
     , COUNT(*) FILTER (WHERE marking = 'Not Received') AS "Not Received"
FROM invoices
GROUP BY 1,2

2. Full dynamic solution with a large list of marking values :

This proposal is an alternative solution to the crosstab solution as proposed in A and B.

The proposed solution here just requires a dedicated composite type which can be dynamically created and then it relies on the jsonb type and standard functions :

Starting from your query which counts the number of rows per year, month and marking value :

• Using the jsonb_object_agg function, the resulting rows are first
  aggregated by year and month into jsonb objects whose jsonb keys
  correspond to the marking values and whose jsonb values
  correspond to the counts.
• the resulting jsonb objects are then converted into records using the jsonb_populate_record function and the dedicated composite type.

First we dynamically create a composite type which corresponds to the ordered list of marking values :

CREATE OR REPLACE PROCEDURE create_composite_type() LANGUAGE plpgsql AS $$
DECLARE
  column_list text ;
BEGIN
  SELECT string_agg(DISTINCT quote_ident(marking) || ' bigint', ',' ORDER BY quote_ident(marking) || ' bigint' ASC)
    INTO column_list
    FROM invoices ;
  
  EXECUTE 'DROP TYPE IF EXISTS composite_type' ;
  EXECUTE 'CREATE TYPE composite_type AS (' || column_list || ')' ;
END ;
$$ ;

CALL create_composite_type() ;

Then the expected result is provided by the following query :

SELECT a.year
     , TO_CHAR(a.year_month, 'Month') AS month
     , (jsonb_populate_record( null :: composite_type
                             , jsonb_object_agg(a.marking, a.count)
                             )
       ).*
 FROM
    ( SELECT year
           , date_trunc('month', creation_date) AS year_month
           , marking
           , count(*) AS count
        FROM invoices AS v
       GROUP BY 1,2,3
    ) AS a
GROUP BY 1,2
ORDER BY month

Obviously, if the list of marking values may vary in time, then you have to recall the create_composite_type() procedure just before executing the query. If you don't update the composite_type, the query will still work (no error !) but some old marking values may be obsolete (not used anymore), and some new marking values may be missing in the query result (not displayed as columns).

See the full demo in dbfiddle.

How to create a pivot table in Postgresql using case when?

The function strftime() is used to extract various parts of a date in SQLite, but is not supported by Postgresql.

Use date_part():

select campaign,
  sum(case when date_part('year', date) = '2019' then revenue else 0 end) as "2019",
  sum(case when date_part('year', date) = '2018' then revenue else 0 end) as "2018"
from df
group by campaign

Or use Postgresql's FILTER clause:

select campaign,
  sum(revenue) filter (where date_part('year', date) = '2019') as "2019",
  sum(revenue) filter (where date_part('year', date) = '2018') as "2018"
from df
group by campaign

Also, don't use single quotes for table/column names.

SQLite allows it but Postgresql does not.

It accepts only double quotes which is the SQL standard.

See the demo.

---

Related Topics