我很难理解如何从我正在使用的数据库中正确转置该表。该表是由几年前设计数据库的人创建的，但我设法使用pg_dump.
这是带有示例条目的表：
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]
列中的问题 idquestion_id代表以下内容：
30 -- not_foot_count
31 -- not_moving
32 -- foot
33 -- bicycle
34 -- motorcycle
35 -- car
36 -- bus
37 -- metro
38 -- other
39 -- train
响应可以是文本（错误的用户响应），但主要是 a1或 a 0（我感兴趣）。
所以我想把这个表转置成一个新表survey，这样返回的查询结果对于每一列都会有对应的响应码值作为列名（32 -> foot; 33 -> bike; 35 -> car.等）
我对所有这些回复都不感兴趣，但 5 : foot, bike(for bike), bus, car, and metro.
因为我在仅检索 5 个感兴趣的响应时遇到了很大的麻烦，所以我开始检索所有这些值以查看我是否正确地做事。事实证明我做错了。
这是我的尝试：
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));
这使：
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
请注意，这不正确，因为列car应该是1.
此外，我并不是对所有的价值观都不感兴趣。相反，只希望是感兴趣的值。
预计出局
我希望将我的返回结果限制为以下（正确答案）：
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
注意：此dbfiddle说明了我的尝试。
编辑
就评论而言，已编辑问题以显示完整的预期输出。
我不喜欢 crosstab() 函数，因为我发现它比过滤聚合更复杂（并且它不能解决您需要手动指定所有结果列的事实）。
以下返回您想要的。