MySQL 如何使用SQL将垂直数据转换为水平数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4071811/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to transform vertical data into horizontal data with SQL?
提问by Dan
I have a table "Item" with a number of related items, like so:
我有一个包含许多相关项目的表“项目”，如下所示：
ID Rel_ID Name RelRank
--- ------ ---- -------
1 1 foo 1
2 1 bar 2
3 1 zam 3
4 2 foo2 1
I'm trying to get a query so items with the same Rel_ID would appear in the same row, like so:
我正在尝试获取一个查询，因此具有相同 Rel_ID 的项目将出现在同一行中，如下所示：
Rel_ID Name1 Name2 Name3
------ ----- ----- -----
1 foo bar zam
2 foo2
I've tried selecting the table multiple times:
我试过多次选择表格：
SELECT k.Rel_ID, k.name 'Name1', k2.name 'Name2'
FROM item k, item k2
WHERE k.Rel_ID = k2.Rel_ID
But this fails. Surely there's a transformation or query that could drastically simplify the process, and I'm just missing it because I haven't used SQL in this way before. What am I missing?
但这失败了。肯定有一个转换或查询可以大大简化这个过程，我只是错过了它，因为我以前没有以这种方式使用过 SQL。我错过了什么？
[Edit: added RelRank column, which does appear in my data]
[编辑：添加了 RelRank 列，它确实出现在我的数据中]
回答by Falcon
Regardless of the database you are using, the concept of what you are trying to achieve is called "Pivot Table".
无论您使用哪种数据库，您尝试实现的概念都称为“数据透视表”。
Here's an example for mysql: http://en.wikibooks.org/wiki/MySQL/Pivot_table
这是 mysql 的示例：http: //en.wikibooks.org/wiki/MySQL/Pivot_table
Some databases have builtin features for that, see the links below.
一些数据库为此具有内置功能，请参阅下面的链接。
SQLServer: http://msdn.microsoft.com/de-de/library/ms177410.aspx
SQLServer：http: //msdn.microsoft.com/de-de/library/ms177410.aspx
Oracle: http://www.dba-oracle.com/t_pivot_examples.htm
甲骨文：http: //www.dba-oracle.com/t_pivot_examples.htm
You can always create a pivot by hand. Just select all the aggregations in a result set and then select from that result set. Note, in your case, you can put all the names into one column using concat (i think that's group_concat in mysql), since you cannot know how many names are related to a a rel_id.
您始终可以手动创建枢轴。只需选择结果集中的所有聚合，然后从该结果集中进行选择。请注意，在您的情况下，您可以使用 concat 将所有名称放入一列（我认为这是 mysql 中的 group_concat），因为您无法知道有多少名称与 aa rel_id 相关。
pseudo-select for your case (i don't know mysql):
为您的情况进行伪选择（我不知道 mysql）：
select rel_id, group_concat(name) from item group by rel_id
回答by smartnut007
I think you are looking for a mysql specific answer. Keep in mind that the syntax could vary across different data stores.
我认为您正在寻找特定于 mysql 的答案。请记住，语法可能因不同的数据存储而异。
MySQL has a feature that makes this easy.
MySQL 有一个功能可以使这变得容易。
SELECT Rel_ID, GROUP_CONCAT(Name SEPARATOR ' ') As Names FROM Item GROUP BY Rel_ID;
that should work :-)
那应该工作:-)
回答by sayannayas
if the names that you listed are static,my below query that i runned sucessfully in sqlfiddle will work
如果您列出的名称是静态的，那么我在 sqlfiddle 中成功运行的以下查询将起作用
SELECT rel_id,
MAX (DECODE (rel_id, '1', DECODE (relrank, '1', name) , '2',DECODE (relrank, '1', name))) NAME1,
MAX (DECODE (rel_id, '1', DECODE (relrank, '2', name))) NAME2,
MAX (DECODE (rel_id, '1', DECODE (relrank, '3', name))) NAME3
FROM supportContacts
GROUP BY rel_id
heres the SQL fiddle
继承人的 SQL 小提琴
