PostgreSQL 9.3: Dynamic pivot table

Question

I have a table called as matrix which contains two columns namely cola and colb as shown below:

Table: matrix

create table matrix
(
cola varchar(10),
colb varchar(10)
);

Insertion of rows:

insert into matrix values('A1','B1'),('A2','B2'),('A3','B3'),('A4','B4'),
             ('A5','B5'),('A6','B6'),('A7','B7'),('A8','B8'),
             ('A9','B9'),('A10','B10'),('A11','B11'),('A12','B12'),
             ('A13','B13'),('A14','B14'),('A15','B15'),('A16','B16'),
             ('A17','B17'),('A18','B18'),('A19','B19'),('A20','B20'),
             ('A21','B21'),('A22','B22'),('A23','B23'),('A24','B24'),
             ('A25','B25'),('A26','B26'),('A27','B27'),('A28','B28'),
             ('A29','B29'),('A30','B30');

Note: I want to show the result in the form of matrix and count which columns belongs to each others and assign the values in the matrix for each column. I have just added 30 records for just example but there may be thousands of records also. So I need to prepare a dynamic pivot table for that. The expected result as shown below.

Expected Result:

      A1        A2      A3      A4      A5      A6 ................ A30
      ------------------------------------------------------------------
B1 |  1         0       0       0       0       0                    0  
   |    
B2 |  0         1       0       0       0       0                    0
   |
B3 |  0         0       1       0       0       0                    0 
   |
B4 |  0         0       0       1       0       0                    0
   | 
B5 |  0         0       0       0       1       0                    0 
   | 
B6 |  0         0       0       0       0       1                    0
.  |
.  |
.  |
.  |
B30|  0         0       0       0       0        0                   1

You can have a play with the crosstab function and see if that works for you. http://www.postgresql.org/docs/9.4/static/tablefunc.html — mlinth, Feb 05 '15 at 09:20

score 2 · Accepted Answer · edited May 23 '17 at 12:15

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:

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.

@MAK: About "dynamic": That's *not possible* in a single query. You need two queries. First build the statement, then execute it. You are not the first to ask: http://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by/15514334#15514334 Or you return arrays instead of individual columns. That *is* possible. — Erwin Brandstetter, Mar 15 '15 at 02:21
Nope! Because I have 53000 records to show in a columns. Due to limit of columns `1600` I am unable to show in a pivot table. — MAK, Mar 18 '15 at 07:36
The [link](http://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by/15514334#15514334) which you have provided in your first comment in that the user is just looking for 3 columns that is `val1,val2,val3`. But here I have limitless columns. — MAK, Mar 18 '15 at 07:41
I am getting an error near by second `ORDER` that is `ORDER BY right(cola, -1)::int`. — MAK, Mar 18 '15 at 08:14
@MAK: Ah, sorry, that was `ORDER BY` twice. Added some improvements and a simple matrix while being at it. — Erwin Brandstetter, Mar 18 '15 at 13:00

PostgreSQL 9.3: Dynamic pivot table

1 Answers1

Unknown number of categories

Too many categories

Basic matrix

Linked