sql - How can I create columns in postgres if I do not know how many it is?

Question

My problem is: I have a query that returns this table:

Result of my query

Explaining the table: it shows some questions and their numerical answers (the number 0 can mean No, the number 1 can mean Yes, the number 2 can mean a little and so on) What I want is this table to become this:

What I want

So it brings me how many answers in each answer option I had. However, the number of response options varies. In this example I have options for 0 to 4, but there are cases where the options go from 0 to 10. So, how can I create columns for each of these options?

My query is something like this:

SELECT 
    data,
    id,
    question,
    answer,
    COUNT(*) AS info2,
    COUNT(CASE WHEN answer IS NOT NULL THEN 1 END) AS info3,
    COUNT(CASE WHEN answer IS NULL THEN 1 END) AS info4
FROM table1
JOIN table2
    ON table1.id = table2.id
WHERE table1.variable_y BETWEEN '2020-01-01 00:00:00' AND '2020-05-31 23:59:59'
    AND variable_x = 'ABC123'
    AND variable_z = 'ABC'
GROUP BY 1,2,3,4

Answer 1

You could perform a dynamic query. The best option is to use a programming language, but you can also use the sql language.

I made a reduced example:

Example data:

CREATE TABLE t (
answer int
);

INSERT INTO t VALUES (0);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);
INSERT INTO t VALUES (4);
INSERT INTO t VALUES (3);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (2);

Dynamic query:

(SELECT 'SELECT ')

UNION ALL

(SELECT 
    'SUM(CASE WHEN answer = ' || answer || ' THEN 1 ELSE 0 END) AS res' || answer || ','
FROM t
GROUP BY answer
ORDER BY answer)

UNION ALL

(SELECT 'COUNT(*) AS info2,
    SUM(CASE WHEN answer IS NOT NULL THEN 1 ELSE 0 END) AS info3,
    SUM(CASE WHEN answer IS NULL THEN 1 ELSE 0 END) AS info4 
    FROM t;')

The output is your desire query:

SELECT 
  SUM(CASE WHEN answer = 0 THEN 1 ELSE 0 END) AS res0,
  SUM(CASE WHEN answer = 1 THEN 1 ELSE 0 END) AS res1,
  SUM(CASE WHEN answer = 2 THEN 1 ELSE 0 END) AS res2,
  SUM(CASE WHEN answer = 3 THEN 1 ELSE 0 END) AS res3,
  SUM(CASE WHEN answer = 4 THEN 1 ELSE 0 END) AS res4,
  COUNT(*) AS info2,
  SUM(CASE WHEN answer IS NOT NULL THEN 1 ELSE 0 END) AS info3,
  SUM(CASE WHEN answer IS NULL THEN 1 ELSE 0 END) AS info4 
FROM t;

Query result (result of execute your desire query):

res0	res1	res2	res3	res4	info2	info3	info4
1	1	3	2	1	8	8	0