Estou tendo dificuldade em entender como transpor corretamente esta tabela de um banco de dados que estou usando. Esta tabela foi criada por alguém que projetou o banco de dados anos atrás, mas consegui recuperar a instrução create da tabela usando pg_dump.
Aqui está a tabela com uma entrada de exemplo:
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]
O ID da pergunta na question_idcoluna significa o seguinte:
30 -- not_foot_count
31 -- not_moving
32 -- foot
33 -- bicycle
34 -- motorcycle
35 -- car
36 -- bus
37 -- metro
38 -- other
39 -- train
A resposta pode ser texto (resposta errada do usuário), mas principalmente a 1ou a 0(que são do meu interesse).
Então eu quero transpor esta tabela para uma nova tabela survey, para que o resultado da consulta retornada tenha para cada coluna, o valor do código de resposta correspondente como o nome da coluna ( 32 -> foot; 33 -> bike; 35 -> car. etc)
Não estou interessado em todas essas respostas, mas 5 : foot, bike(para bicicleta), bus, car, e metro.
Como tive grande dificuldade em recuperar apenas as 5 respostas de interesse, comecei recuperando todos esses valores para ver se estou fazendo as coisas corretamente. Acontece que estou fazendo as coisas erradas.
Segue minha tentativa:
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));
Que dá:
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
Observe que isso não está correto como a coluna cardeveria estar 1.
Além disso, não estou interessado em todos os valores. Em vez disso, gostaria que apenas os valores de interesse fossem.
Esperado para fora
Espero limitar meu resultado de retorno ao seguinte (com a resposta correta):
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
Nota: Minha tentativa é ilustrada neste dbfiddle .
EDITAR
Quanto aos comentários, pergunta editada para mostrar a saída esperada completa.
Não sou fã da função crosstab(), pois acho mais complicada do que a agregação filtrada (e não resolve o fato de que você precisa especificar todas as colunas de resultados manualmente).
O seguinte retorna o que você deseja.