SQL转置行作为列

问题描述

我有一个有趣的难题，我相信可以用纯SQL来解决.我有类似于以下表格:

I have an interesting conundrum which I believe can be solved in purely SQL. I have tables similar to the following:

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中做的，老实说我不记得我们是怎么做到的.

I don't know how many questions there will be, and they will be dynamic, so I can't just code for every question. I am using PostgreSQL and I believe this is called transposition, but I can't seem to find anything that says the standard way of doing this in SQL. I remember doing this in my database class back in college, but it was in MySQL and I honestly don't remember how we did it.

我假设它将是联接和GROUP BY语句的组合，但我什至不知道如何开始.

I'm assuming it will be a combination of joins and a GROUP BY statement, but I can't even figure out how to start.

有人知道该怎么做吗?非常感谢！

Anybody know how to do this? Thanks very much!

我发现了一些有关使用 crosstab 似乎是我想要的，但是我很难理解它.链接到更好的文章将不胜感激！

Edit 1: I found some information about using a crosstab which seems to be what I want, but I'm having trouble making sense of it. Links to better articles would be greatly appreciated!

推荐答案

使用:

  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

这是标准的数据透视查询，因为您是将数据从行透视"为列数据.

This is a standard pivot query, because you are "pivoting" the data from rows to columnar data.

这篇关于SQL转置行作为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！