Postgresql 9.3: Dynamic Pivot Table

PostgreSQL 9.3: Dynamic pivot table

You can do this with crosstab() from the additional module tablefunc:

SELECT b
     , COALESCE(a1, 0) AS "A1"
     , COALESCE(a2, 0) AS "A2"
     , COALESCE(a3, 0) AS "A3"
     , ... -- all the way up to "A30"
FROM   crosstab(
         'SELECT colb, cola, 1 AS val FROM matrix
          ORDER  BY 1,2'
        , $$SELECT 'A'::text || g FROM generate_series(1,30) g$$
       ) AS t (b text
             , a1  int, a2  int, a3  int, a4  int, a5  int, a6  int
             , a7  int, a8  int, a9  int, a10 int, a11 int, a12 int
             , a13 int, a14 int, a15 int, a16 int, a17 int, a18 int
             , a19 int, a20 int, a21 int, a22 int, a23 int, a24 int
             , a25 int, a26 int, a27 int, a28 int, a29 int, a30 int);

If NULL instead of 0 works, too, it can be just SELECT * in the outer query.

Detailed explanation:

• PostgreSQL Crosstab Query

The special "difficulty" here: no actual "value". So add 1 AS val as last column.

Unknown number of categories

A completely dynamic query (with unknown result type) is not possible in a single query. You need two queries. First build a statement like the above dynamically, then execute it. Details:

• Selecting multiple max() values using a single SQL statement

• PostgreSQL convert columns to rows? Transpose?

• Dynamically generate columns for crosstab in PostgreSQL

• Dynamic alternative to pivot with CASE and GROUP BY

Too many categories

If you exceed the maximum number of columns (1600), a classic crosstab is impossible, because the result cannot be represented with individual columns. (Also, human eyes would hardly be able to read a table with that many columns)

Arrays or document types like hstore or jsonb are the alternative. Here is a solution with arrays:

SELECT colb, array_agg(cola) AS colas
FROM  (
   SELECT colb, right(colb, -1)::int AS sortb
        , CASE WHEN m.cola IS NULL THEN 0 ELSE 1 END AS cola
   FROM        (SELECT DISTINCT colb FROM matrix) b
   CROSS  JOIN (SELECT DISTINCT cola FROM matrix) a
   LEFT   JOIN matrix m USING (colb, cola)
   ORDER  BY sortb, right(cola, -1)::int 
   ) sub
GROUP  BY 1, sortb
ORDER  BY sortb;

• Build the complete grid of values with:

              (SELECT DISTINCT colb FROM matrix) b
  CROSS  JOIN (SELECT DISTINCT cola FROM matrix) a
  

• LEFT JOIN existing combinations, order by the numeric part of the name and aggregate into arrays.

  • right(colb, -1)::int trims the leading character from 'A3' and casts the digits to integer so we get a proper sort order.

Basic matrix

If you just want a table of 0 an 1 where x = y, this can be had cheaper:

SELECT x, array_agg((x = y)::int) AS y_arr
FROM   generate_series(1,10) x
     , generate_series(1,10) y
GROUP  BY 1
ORDER  BY 1;

SQL Fiddle building on the one you provided in the comments.

Note that sqlfiddle.com currently has a bug that kills the display of array values. So I cast to text there to work around it.

Dynamic pivot query using PostgreSQL 9.3

SELECT *
FROM   crosstab (
   'SELECT ProductNumber, ProductName, Salescountry, SalesQuantity
    FROM   product
    ORDER  BY 1'
, $$SELECT unnest('{US,UK,UAE1}'::varchar[])$$
  ) AS ct (
   "ProductNumber" varchar
 , "ProductName"   varchar
 , "US"   int
 , "UK"   int
 , "UAE1" int);

Detailed explanation:

• PostgreSQL Crosstab Query
• Pivot on Multiple Columns using Tablefunc

Completely dynamic query for varying number of distinct Salescountry?

• Dynamic alternative to pivot with CASE and GROUP BY

PostgreSQL 9.3:Dynamic Cross tab query

A server-side function cannot have a dynamic return type in PostgreSQL, so obtaining the mentioned result as-is from a fixed function is not possible.

Also, it does not look much like a typical crosstab problem, anyway. The cola part of the output can be obtained by filtering over an aggregate, and the other columns A1/A2/A6 are actually the input, so copying them as columns into the output is easy in the context of a client-side generated query.

The gist of an actual SQL query finding the matching rows would be:

select cola from ts
group by cola
having array_agg(colc order by colc)='{A1,A2,A6}'

This will find 101.

Adding the other columns is a just a client-side presentation problem. For instance, the query could be written like this:

select cola, 1 as A1, 1 as A2, 1 as A6 from tst
group by cola
having array_agg(colc order by colc)='{A1,A2,A6}';

result:

 cola | a1 | a2 | a6 
------+----+----+----
 101  |  1 |  1 |  1

PostgreSQL 9.3: Pivot table query

SELECT * FROM crosstab(
      $$SELECT grp.*, e.group_name
             , CASE WHEN e.employee_number IS NULL THEN 0 ELSE 1 END AS val
        FROM  (
           SELECT employee_number
                , count(employee_role)::int            AS total_roles
                , (SELECT count(DISTINCT group_name)::int
                   FROM   employee
                   WHERE  group_name <> '')            AS total_groups
                , count(group_name <> '' OR NULL)::int AS available
                , count(group_name =  '' OR NULL)::int AS others
           FROM   employee
           GROUP  BY 1
           ) grp
        LEFT   JOIN employee e ON e.employee_number = grp.employee_number
                              AND e.group_name <> ''
        ORDER  BY grp.employee_number, e.group_name$$
     ,$$VALUES ('Group_1'::text), ('Group_2')$$
   ) AS ct (employee_number text
          , total_roles  int
          , total_groups int
          , available    int
          , others       int
          , "Group_1"    int
          , "Group_2"    int);

SQL Fiddle demonstrating the base query, but not the crosstab step, which is not installed on sqlfiddle.com

Basics for crosstab:

• PostgreSQL Crosstab Query

Special in this crosstab: all the "extra" columns. Those columns are placed in the middle, after the "row name" but before "category" and "value":

• Pivot on Multiple Columns using Tablefunc

Once again, if you have a dynamic set of groups, you need to build this statement dynamically and execute it in a second call:

• Selecting multiple max() values using a single SQL statement

Postgres pivot without crosstab

You can use filtered aggregation:

select group_code, 
       max(total) filter (where ym = date '2020-09-01') as "2020-09-01",
       max(total) filter (where ym = date '2020-10-01') as "2020-10-01",
       max(total) filter (where ym = date '2020-11-01') as "2020-11-01",
       max(total) filter (where ym = date '2020-12-01') as "2020-11-02"
from the_table
group by group_code;

How to use Pivot in postgres

This is actually an un-pivot, not pivot

select year, week, 'loading' as area, loading as value
from the_table
union all
select year, week, 'picking', picking
from the_table
union all
select year, week, 'painting', painting
from the_table

SQL values for repeating person moved in next column

While what you ask can be accomplished you will find it is not worth the effort as pivoting requires advanced knowledge of the results, well at least the exact size. Since you have varying number of payments you will need to dynamically create the query. Meaning you write a code which looks at the data then writes the query before executing it. That can get very complex very quickly and the result is a maintenance nightmare. Let me propose the following instead.

Your idea to maximum number of payments done by a client in order to determine the number of columns has merit, but let SQL do that work (which it does easily), then let your client's presentation manager handle the pivoting. The following builds one row per customer with an array of payments for each customer and adding columns for number of payments for that customer and the maximum number of payments for any customer. (See demo).

select client_id, payments_made
     , array_length(payments_made,1) num_payments
     , max(array_length(payments_made,1)) over() max_payments
  from (     
         select client_id, array_agg(payment order by paid_date)  payments_made
           from payments 
          group by client_id
       ) gs; 
  

PostgreSQL Crosstab Query With Changing Rows

We can do this using CASE to avoid using sub-queries.

CREATE TABLE organisation  (
  store_name VARCHAR(25),  
  status VARCHAR(25),
  orders INT);
INSERT INTO organisation VALUES
('billys store',     'new'         ,    15),
('billys store',     'ordered'     ,    20),
('billys store',     'canceled'    ,    2),
('johnny store',     'new'         ,    5),
('johnny store',     'out_of_stock',    20),
('rosie store' ,     'new'         ,    6),
('rosie store' ,     'ordered'     ,    4),
('rosie store' ,     'out_of_stock',    10);

8 rows affected

SELECT store_name,
   SUM(CASE WHEN status='new' THEN orders ELSE 0 END) new_,
SUM(CASE WHEN status='canceled' THEN orders ELSE 0 END) canceled,
SUM(CASE WHEN status='ordered' THEN orders ELSE 0 END) ordered,
SUM(CASE WHEN status='new' THEN orders ELSE 0 END) o_o_s
FROM organisation o
GROUP BY store_name; 
GO

store_name   | new | canceled | ordered | o_o_s
:----------- | --: | -------: | ------: | ----:
billys store |  15 |        2 |      20 |    15
johnny store |   5 |        0 |       0 |     5
rosie store  |   6 |        0 |       4 |     6

db<>fiddle here

---

Related Topics