What am I missing in my crosstab query here?

Question

I am having difficulty understanding how to correctly transpose this table from a database I am using. This table was created by someone who designed the database years ago, but I managed to retrieve the table's create statement using pg_dump.

Here's the table with an example entry:

CREATE TABLE response (
    session_id integer NOT NULL,
    seconds integer NOT NULL,
    question_id integer NOT NULL,
    response character varying(500),
    file bytea
);

INSERT INTO response(session_id, seconds, question_id, response, file)
VALUES (758,1459505869,31,'0',''),  (758,1459505869,32,'0',''), 
(758,1459505869,33,'0',''), (758,1459505869,34,'0',''), 
(758,1459505869,35,'1',''), (758,1459505869,36,'0',''), 
(758,1459505869,37,'0',''), (758,1459505869,38,'0',''), 
(758,1459506973,38,'0',''), (758,1459506973,37,'0',''), 
(758,1459506973,36,'0',''),(758,1459506973,35,'1',''),  
(758,1459506973,34,'0',''),(758,1459506973,33,'0',''),  
(758,1459506973,32,'0',''),(758,1459506973,31,'0',''),
(758,1459508676,31,'0',''),(758,1459508676,32,'0',''),  
(758,1459508676,33,'0',''),(758,1459508676,34,'0',''),  
(758,1459508676,35,'1',''),(758,1459508676,36,'0',''),  
(758,1459508676,37,'0', ''),    (758,1459508676,38,'0', '');

SELECT * FROM response LIMIT 5;
session_id  seconds   question_id   response    file
   758     1459505869     31           0         [null]
   758     1459505869     32           0         [null]
   758     1459505869     33           0         [null]
   758     1459505869     34           0         [null]
   758     1459505869     35           1         [null]

The question id in the question_id column stands for the following:

30  -- not_foot_count 
31  -- not_moving
32  -- foot
33  -- bicycle
34  -- motorcycle
35  -- car
36  -- bus
37  -- metro
38  -- other
39  -- train

Response can be text (wrong user response), but mostly a 1 or a 0 (which are of interest to me).

So I want to transpose this table into a new table survey, so that the returned query result will have for each column, the corresponding response code value as the column name (32 -> foot; 33 -> bike; 35 -> car. etc)

I am not interested in all these responses, but 5: foot, bike (for bicycle), bus, car, and metro.

Because I had great trouble retrieving only the 5 responses of interest, I started with retrieving all this values to see if I am doing things correctly. It turns out that I am doing things wrong.

Here's my attempt:

CREATE TABLE survey
AS
SELECT aresult.session_id,
   aresult.not_foot_count,
    aresult.not_moving,
    aresult.foot,
    aresult.bike,
    aresult.motor,
    aresult.car,
    aresult.bus,
    aresult.metro,
    aresult.train,
    aresult.other
   FROM crosstab('select session_id, question_id, response 
                from  response
                order by session_id,question_id'::text) 
  aresult(session_id integer, not_foot_count character varying(500), 
  not_moving character varying(500), foot character varying(500), 
  bike character varying(500), motor character varying(500), 
  car character varying(500), bus character varying(500), 
  metro character varying(500), train character varying(500), 
  other character varying(500));

Which gives:

SELECT * FROM survey;
session_id seconds not_foot_count not_moving foot bike motor car bus metro train other
758       1459505869       0          0        0   0    0     0   0   0      0     0
758       1459506973       0          0        0   0    0     0   0   0      0     0
758       1459508676       0          0        0   0    0     0   0   0      0     0

Notice that this is not correct as the column car should be 1.

Besides, I am not not interested in all the values. Instead would like only values of interest to be.

Expected out

I expect to limit my return result to the following (with the correct answer):

session_id  seconds    foot bike car bus metro 
    758   1459505869    0     0   1   0    0
    758   1459506973    0     0   1   0    0
    758   1459508676    0     0   1   0    0

Note: My attempt is illustrated in this dbfiddle.

EDIT

As far comments, question edited to show complete expected output.

Answer 1

I am not a fan of the crosstab() function as I find it more complicated than filtered aggregation (and it doesn't workaround the fact that you need to specify all result columns manually).

The following returns what you want.

select session_id, 
       seconds, 
       max(response) filter (where question_id = 32) as foot,
       max(response) filter (where question_id = 33) as bike,
       max(response) filter (where question_id = 36) as bus,
       max(response) filter (where question_id = 35) as car,
       max(response) filter (where question_id = 37) as metro
from response
group by session_id, seconds