PostgreSQL 9.3: Dynamic pivot table

Solution 1:

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.