PostgreSQL 9.3: Dynamic Pivot Table

Question

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

Table : Matrix

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

Insert lines :

 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 matrix form and calculate which columns belong to each other, and assign values ​​in the matrix for each column. I just added 30 entries for example, but there can also be thousands of entries. So I need to prepare a dynamic pivot table. 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 

Answer 1

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

 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 also works, it could just be SELECT * in the outer query.
Detailed explanation:

• PostgreSQL crosstab query

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

Unknown number of categories

A fully dynamic query (with an unknown result type) is not possible in a single query. You need two queries. First create the statement as described above and execute it. Details:

• Select multiple max () values ​​using a single SQL statement

• Does PostgreSQL convert columns to rows? Transpose?

• Dynamically create columns for a crosstab in PostgreSQL

• A dynamic alternative to rotation with CASE and GROUP BY

Too many categories

If you exceed the maximum number of columns (1600) , a classic crosstab is not possible because the result cannot be represented by individual columns. (In addition, human eyes are unlikely to be able to read a table with so many columns)

Arrays or document types like hstore or jsonb are an 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; 

• Create a complete grid of values ​​with:

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

• LEFT JOIN existing combinations, arrange the numerical part of the name and aggregate into arrays.

  • right(colb, -1)::int truncates the leading character from "A3" and discards the digits to integers, so we get the correct sort order.

Base matrix

If you need a table 0 a 1 , where x = y , this might be 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 based on what you provided in the comments.

Please note that sqlfiddle.com currently has an error that kills the display of array values. So I applied text to get around it.