如何使用SQL将垂直数据转换为水平数据? [英] How to transform vertical data into horizontal data with SQL?

问题描述

我有一个表"Item"，其中包含许多相关项，例如:

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.

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

您始终可以手动创建枢轴.只需选择结果集中的所有聚合，然后从该结果集中选择即可.请注意，在您的情况下，您可以使用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将垂直数据转换为水平数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！