-1

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
Helena Maia
  • 23
  • 6
  • Does this answer your question? [Dynamic number of columns in select clause in SQL Server 2008](https://stackoverflow.com/questions/60108423/dynamic-number-of-columns-in-select-clause-in-sql-server-2008) – Luuk Apr 19 '21 at 18:15
  • 1
    Welcome to the SO community. Please spend a few minutes to take the [Tour](https://stackoverflow.com/tour) and to review [ask]. Specifically, show complete table definition (ddl) and sample date, as text -**no images** (or provide a [fiddle](https://dbfiddle.uk/) and the expected results of that data - also as text. I did notice you wanted results show "... res0, res1... info2, info3..." but query only returns "...info2, info3...". What are the 'res...' columns and their source? – Belayer Apr 19 '21 at 18:33
  • Simply put: you can't. One of the fundamental restrictions of SQL is that all columns of a query must be known **before** the query is actually executed. So the number, name and data type of each column must be evaluated when parsing the statement. You will have to write as many expressions as you can have columns. e.g. write 50 of them to be prepared. This kind of formatting (transposing rows to columns) is much better done in the front end. SQL is simply not designed for this. –  Apr 19 '21 at 18:44
  • Hi @Luuk. My problem is in postgresql, so it does not help me. – Helena Maia Apr 19 '21 at 18:50
  • @HelenaMaia: I do think postgresql also support dynamic sql ? see: [1](https://www.postgresql.org/docs/13/ecpg-dynamic.html) and [2](https://stackoverflow.com/questions/11948131/postgresql-writing-dynamic-sql-in-stored-procedure-that-returns-a-result-set) – Luuk Apr 19 '21 at 21:19
  • Hi @Luuk I do not understand dynamic queries. Can you pls explain me? – Helena Maia Apr 20 '21 at 13:25
  • You can create a string, which hold an SQL statement. If you create that string it can contain a dynamic query. Most of the times such a thing can be done in a stored procedure. the basics are explained here: https://www.postgresql.org/docs/13/sql-prepare.html – Luuk Apr 20 '21 at 13:32
  • Hi @a_horse_with_no_name :) Thanks for your reply. Can you pls explain me what do you mean with "So the number, name and data type of each column must be evaluated when parsing the statement. You will have to write as many expressions as you can have columns. e.g. write 50 of them to be prepared." - I do not understand how would I write expressions to be prepared. – Helena Maia May 07 '21 at 13:18

1 Answers1

0

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
nachospiu
  • 2,009
  • 2
  • 8
  • 12
  • Hi there! thanks for your suggestion. But I do not understand dynamic queries. I am supposed to apply the dynamic query with no modification? I do not understar these statements between ' ' -> example: 'SUM(CASE WHEN answer = ' || answer || ' THEN 1 ELSE 0 END) AS res' || answer || ',' – Helena Maia Apr 20 '21 at 13:28
  • Can you explain more? :) – Helena Maia Apr 20 '21 at 13:28
  • Hi @HelenaMaia, you are welcome! Dynamic query means that you can write a query using an algorithm, in this case I used a sql query to create another query (the query that will return the desire result), It easier to use a programming language to do this. The first query create a second query, an if you execute the second query, you will get the desire output. I made a little example to show you the idea. Try to execute my example to understand it. – nachospiu Apr 20 '21 at 14:01