问题描述
我有一个包含许多相关项目的表格项目"，如下所示:
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
我正在尝试获取一个查询，因此具有相同 Rel_ID 的项目将出现在同一行中，如下所示:
I'm trying to get a query so items with the same Rel_ID would appear in the same row, like so:
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
但这失败了.当然有一个转换或查询可以大大简化这个过程，我只是想念它，因为我以前没有以这种方式使用过 SQL.我错过了什么?
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?
推荐答案
不管你使用什么数据库，你想要实现的概念叫做数据透视表".
Regardless of the database you are using, the concept of what you are trying to achieve is called "Pivot Table".
以下是 mysql 的示例:http://en.wikibooks.org/wiki/MySQL/Pivot_table
Here's an example for mysql: http://en.wikibooks.org/wiki/MySQL/Pivot_table
一些数据库具有内置功能，请参阅下面的链接.
Some databases have builtin features for that, see the links below.
SQL服务器:http://msdn.microsoft.com/de-de/library/ms177410.aspx
甲骨文:http://www.dba-oracle.com/t_pivot_examples.htm
您始终可以手动创建枢轴.只需选择结果集中的所有聚合，然后从该结果集中进行选择.请注意，在您的情况下，您可以使用 concat 将所有名称放入一列(我认为这是 mysql 中的 group_concat)，因为您不知道有多少名称与 rel_id 相关.
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.
为您的案例伪选择(我不知道 mysql):
pseudo-select for your case (i don't know mysql):
select rel_id, group_concat(name) from item group by rel_id
这篇关于如何用 SQL 将垂直数据转换为水平数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！