如何通过SQL将表中的垂直字段转换为水平结果 [英] how to transform vertical fields in a table to horizontal result by SQL
问题描述
我有一个这样的表:
create table t1 {
person_id int,
item_name varchar(30),
item_value varchar(100)
};
假设person_id + item_name是组合键,现在我在表t1中有一些数据(5条记录),如下所示:
Suppose person_id+item_name is the composite key, now I have some data (5 records) in table t1 as below:
person_id ====item_name ====== item_value
1 'NAME' 'john'
1 'GENDER' 'M'
1 'DOB' '1970/02/01'
1 'M_PHONE' '1234567890'
1 'ADDRESS' 'Some Addresses unknown'
现在,我想使用SQL(或组合存储过程/函数或其他方法)来查询上述结果(1个结果集)成为:
Now I want to use SQL (or combing store procedure/function or whatever) to query the above result (1 result set) become:
NAME==GENDER==DOB========M_PHONE=======ADDRESS===============
1 M 1970/02/01 1234567890 Some Addresses unknown
我该怎么办? 谢谢您的帮助.
How should I do ? Thank you for your help.
推荐答案
无论使用哪种数据库,都将尝试实现的概念称为数据透视表".
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),因为您不知道有多少名称与person_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 person_id.
这篇关于如何通过SQL将表中的垂直字段转换为水平结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!