SQL 将行转置为列 [英] SQL Transpose Rows as Columns
问题描述
我有一个有趣的难题,我相信它可以用纯 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!
编辑 1: 我发现了一些关于使用 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 将行转置为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!