oracle Oracle中如何将行转换为列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10498214/
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 convert rows into columns in Oracle?
提问by sant
I have table with data like shown below in Oracle database.
我在 Oracle 数据库中有如下所示的数据表。
emp_num person_name organization earned_date sum(hours)
------- ----------- ------------ ----------- ----------
36372 Name1 Test1 23-MAR-11 3.17
36372 Name1 Test1 15-MAR-11 6.70
40208 Name2 Test2 08-APR-11 13.50
40208 Name2 Test2 06-APR-11 12.07
I need to change query output like below. How can I do that?
我需要更改查询输出，如下所示。我怎样才能做到这一点？
emp_num person_name organization 23-MAR-11 15-MAR-11 08-APR-11 06-APR-11
------- ----------- ------------ --------- --------- --------- ---------
36372 Name1 Test1 3.17 6.70
40208 Name2 Test2 13.50 12.70
回答by Gordon Linoff
You can't dynamically name columns in a table, unless you use some form of dynamic SQL. However, you can get what you want using generic date columns:
您不能动态命名表中的列，除非您使用某种形式的动态 SQL。但是，您可以使用通用日期列获得所需内容：
select emp_num, person_name, organization,
sum(decode(datenum, 1, hours, 0)) as date1hours,
sum(decode(datenum, 2, hours, 0)) as date2hours,
...
min(decode(datenum, 1, earned_date) as date1,
min(decode(datenum, 2, earned_date) as date2,
...
from
(
select t.*,
dense_rank() over (partition by NULL order by earned_date) as datenum
from the_table t
) t
group by emp_num, person_name, organization
By the way, Oracle 10g supports the CASEsyntax, and I would advise you to use that instead of decode.
顺便说一句，Oracle 10g 支持CASE语法，我建议您使用它而不是decode.
回答by Andrew Wood
select
emp_num,
person_name,
organization,
sum(decode(earned_date,to_date('23/03/2011','dd/mm/yyyy'),hours,0)) 23mar11,
sum(decode(earned_date,to_date('15/03/2011','dd/mm/yyyy'),hours,0)) 15mar11,
sum(decode(earned_date,to_date('08/04/2011','dd/mm/yyyy'),hours,0)) 08apr11,
sum(decode(earned_date,to_date('06/04/2011','dd/mm/yyyy'),hours,0)) 06apr11
from
the_table //don't know the name
group by
emp_num,
person_name,
organization
Always use the to_date function to compare dates to strings, I have used a common UK format here.
始终使用 to_date 函数将日期与字符串进行比较，我在这里使用了一种常见的英国格式。
