SQL将行转置为列

Question

我有一个有趣的难题，我相信它可以纯粹地用SQL解决。我有类似以下表格的表：

responses:

user_id | question_id | body
----------------------------
1       | 1           | Yes
2       | 1           | Yes
1       | 2           | Yes
2       | 2           | No
1       | 3           | No
2       | 3           | No


questions:

id | body
-------------------------
1 | Do you like apples?
2 | Do you like oranges?
3 | Do you like carrots?

我想要获得以下输出

user_id | Do you like apples? | Do you like oranges? | Do you like carrots?
---------------------------------------------------------------------------
1       | Yes                 | Yes                  | No
2       | Yes                 | No                   | No

我不知道会有多少个问题，而且它们是动态的，所以我不能为每个问题编写代码。 我正在使用PostgreSQL，我认为这被称为转置，但是我似乎找不到任何说明在SQL中标准的方式。 我记得在大学的数据库课程中做过这个，但那是在MySQL中，我真的不记得我们是如何做的。

我假设这将是连接和GROUP BY语句的组合，但我甚至无法弄清楚如何开始。

有人知道如何做吗？ 非常感谢！

编辑1：我发现了一些关于使用交叉表的信息，这似乎就是我想要的，但我很难理解它。 请提供更好的文章链接，将不胜感激！

- Topher Fangio

Answer 1

使用：

  SELECT r.user_id,
         MAX(CASE WHEN r.question_id = 1 THEN r.body ELSE NULL END) AS "Do you like apples?",
         MAX(CASE WHEN r.question_id = 2 THEN r.body ELSE NULL END) AS "Do you like oranges?",
         MAX(CASE WHEN r.question_id = 3 THEN r.body ELSE NULL END) AS "Do you like carrots?"
    FROM RESPONSES r
    JOIN QUESTIONS q ON q.id = r.question_id
GROUP BY r.user_id

这是一个标准的数据透视查询，因为你正在将数据从行转换为列式数据。

- OMG Ponies

---

你的意思是说我需要根据问题的数量构建一个动态查询？我想我可以这样做，但我希望有一个更简单的解决方案。 - Topher Fangio

感谢您的回答。看起来这将是最容易实现的，即使我必须在运行时生成查询。 - Topher Fangio

如果有这样一种情况，即出现了一个用户尚未回答任何问题（既不是“是”也不是“否”），而我只想添加该用户并为问题列添加空值，那该怎么办？ - Ahmadov

如果您有动态行数（不是3），那么该如何进行转置？ - UserBSS1

请看下面我的答案，这是一个完全动态的解决方案。 - Hannes Landeholm

Answer 2

我实现了一个真正动态的函数来处理这个问题，无需硬编码任何特定类别的答案或使用外部模块/扩展。它还可以完全控制列排序并支持多个关键字和类/属性列。

您可以在此处找到它：https://github.com/jumpstarter-io/colpivot

解决此特定问题的示例：

begin;

create temporary table responses (
    user_id integer,
    question_id integer,
    body text
) on commit drop;

create temporary table questions (
    id integer,
    body text
) on commit drop;

insert into responses values (1,1,'Yes'), (2,1,'Yes'), (1,2,'Yes'), (2,2,'No'), (1,3,'No'), (2,3,'No');
insert into questions values (1, 'Do you like apples?'), (2, 'Do you like oranges?'), (3, 'Do you like carrots?');

select colpivot('_output', $$
    select r.user_id, q.body q, r.body a from responses r
        join questions q on q.id = r.question_id
$$, array['user_id'], array['q'], '#.a', null);

select * from _output;

rollback;

这将输出：

 user_id | 'Do you like apples?' | 'Do you like carrots?' | 'Do you like oranges?' 
---------+-----------------------+------------------------+------------------------
       1 | Yes                   | No                     | Yes
       2 | Yes                   | No                     | No

- Hannes Landeholm

---

非常好！感谢您的分享并将其开源！不过，希望能看到一些关于它性能的基准测试（特别是在SO上，因为新的搜索者会想要对其能力有信心）。 - Topher Fangio

如何在列名中去掉引号？ - Diego

Answer 3

你可以使用crosstab函数解决这个例子。

drop table if exists responses;
create table responses (
user_id integer,
question_id integer,
body text
);

drop table if exists questions;
create table questions (
id integer,
body text
);

insert into responses values (1,1,'Yes'), (2,1,'Yes'), (1,2,'Yes'), (2,2,'No'), (1,3,'No'), (2,3,'No');
insert into questions values (1, 'Do you like apples?'), (2, 'Do you like oranges?'), (3, 'Do you like carrots?');

select * from crosstab('select responses.user_id, questions.body, responses.body from responses, questions where questions.id = responses.question_id order by user_id') as ct(userid integer, "Do you like apples?" text, "Do you like oranges?" text, "Do you like carrots?" text);

首先，您必须安装tablefunc扩展。自9.1版本以来，您可以使用create extension进行安装：

CREATE EXTENSION tablefunc;

- Francisco Puga

Answer 4

我写了一个生成动态查询的函数。它会为交叉表生成SQL并创建一个视图（如果存在则先删除）。然后您可以从该视图中选择以获取结果。

以下是该函数：

CREATE OR REPLACE FUNCTION public.c_crosstab (
  eavsql_inarg varchar,
  resview varchar,
  rowid varchar,
  colid varchar,
  val varchar,
  agr varchar
)
RETURNS void AS
$body$
DECLARE
    casesql varchar;
    dynsql varchar;    
    r record;
BEGIN   
 dynsql='';

 for r in 
      select * from pg_views where lower(viewname) = lower(resview)
  loop
      execute 'DROP VIEW ' || resview;
  end loop;   

 casesql='SELECT DISTINCT ' || colid || ' AS v from (' || eavsql_inarg || ') eav ORDER BY ' || colid;
 FOR r IN EXECUTE casesql Loop
    dynsql = dynsql || ', ' || agr || '(CASE WHEN ' || colid || '=''' || r.v || ''' THEN ' || val || ' ELSE NULL END) AS ' || agr || '_' || r.v;
 END LOOP;
 dynsql = 'CREATE VIEW ' || resview || ' AS SELECT ' || rowid || dynsql || ' from (' || eavsql_inarg || ') eav GROUP BY ' || rowid;
 RAISE NOTICE 'dynsql %1', dynsql; 
 EXECUTE dynsql;
END

$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

这是我使用它的方式：

SELECT c_crosstab('query_txt', 'view_name', 'entity_column_name', 'attribute_column_name', 'value_column_name', 'first');

示例： 首先运行：

SELECT c_crosstab('Select * from table', 'ct_view', 'usr_id', 'question_id', 'response_value', 'first');

Than:

Select * from ct_view;

- SunWuKung

Answer 5

这个例子可以在 contrib/tablefunc/ 中找到。

- Peter Eisentraut

---

嗯，contrib/tablefunc在哪里？你是在说文档服务器上的一个目录吗？ - Topher Fangio

它在源代码树中的那个目录中，或者你可能会发现一个需要安装的包含它的 postgresql-contrib 二进制包。 - Peter Eisentraut

因为回答质量较差而被踩。您没有提供任何上下文，也没有提供源材料的部分内容（以防它发生变化），也没有努力将其与问题联系起来。请参考此链接以获取更好的答案：http://stackoverflow.com/help/how-to-answer - coagmano