转置sql结果，以便一列进入多列 [英] Transposing an sql result so that one column goes onto multiple columns

问题描述

我正在尝试从表格中获取特定格式的数据.但是，由于数据库上的联接太多/太繁重，我的所有尝试似乎都交给了DB.

I'm trying to get data out of a table for a survey in a particular format. However all my attempts seems to hand the DB because of too many joins/too heavy on the DB.

我的数据如下:

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行.我希望结果看起来像这样:

There are roughly 250,000 rows and each user has about 30 rows. I want the result to look like:

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

因此，每个用户在结果中都有一行，每行每个答案都有一个单独的列.

So that each user has one row in the result, each with a separate column for each answer.

我使用的是Postgres，但我可以翻译成Postgres来回答任何SQL语言，所以不胜感激.

I'm using Postgres but answers in any SQL language would be appreciated as I could translate to Postgres.

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

I also need to be able to deal with users not answering questions, i.e. in the example above q2 for user 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)

user是保留字.不要将其用作列名！我将其重命名为usr.

user is a reserved word. Don't use it as column name! I renamed it to usr.

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

You need to install the additional module tablefunc which provides the function crosstab(). Note that this operation is strictly per database. In PostgreSQL 9.1 you can simply:

CREATE EXTENSION tablefunc;

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

For older version you would execute a shell-script supplied in your contrib directory. In Debian, for PostgreSQL 8.4, that would be:

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

这篇关于转置sql结果，以便一列进入多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！