sql - 转置 sql 结果，使一列进入多列

Question

我正在尝试以特定格式从表格中获取数据以进行调查。但是，由于连接太多/数据库负担过重，我所有的尝试似乎都交给了数据库。

我的数据是这样的:

id, user, question_id, answer_id, 
1,   1,   1,           1
3,   1,   3,           15
4,   2,   1,           2
5,   2,   2,           12
6,   2,   3,           20

大约有 250,000 行，每个用户大约有 30 行。我希望结果看起来像:

user0, q1, q2,   q3 
1,     1,  NULL, 15
2,     2,  12,   20 

这样每个用户在结果中都有一行，每个用户的每个答案都有一个单独的列。

我正在使用 Postgres，但如果我可以翻译成 Postgres，任何 SQL 语言的答案都将不胜感激。

编辑:我还需要能够处理不回答问题的用户，即在上面的 q2 示例中，用户 1。

Answer 1

考虑以下演示:

CREATE TEMP TABLE qa (id int, usr int, question_id int, answer_id int);
INSERT INTO qa VALUES
 (1,1,1,1)
,(2,1,2,9)
,(3,1,3,15)
,(4,2,1,2)
,(5,2,2,12)
,(6,2,3,20);

SELECT *
FROM   crosstab('
    SELECT usr::text
          ,question_id
          ,answer_id
    FROM qa
    ORDER BY 1,2')
 AS ct (
     usr text
    ,q1 int
    ,q2 int
    ,q3 int);

结果:

 usr | q1 | q2 | q3
-----+----+----+----
 1   |  1 |  9 | 15
 2   |  2 | 12 | 20
(2 rows)

用户 是 reserved word .不要将它用作列名!我将它重命名为 usr。

您需要安装附加模块tablefunc它提供函数 crosstab()。请注意，此操作严格每个数据库。在 PostgreSQL 9.1 中，您可以简单地:

CREATE EXTENSION tablefunc;

对于旧版本，您将执行 contrib 目录中提供的 shell 脚本。在 Debian 中，对于 PostgreSQL 8.4，这将是:

psql mydb -f /usr/share/postgresql/8.4/contrib/tablefunc.sql