两列在Oracle SQL中枢轴 [英] two columns Pivoting in Oracle SQL

问题描述

我有一个关于在Oracle中透视数据的问题.这是我的数据，就像

I have a question in Pivoting data in Oracle . Here is my data looks like

ID, TaskName, Type, Date
44400 M0 A 1/1/2015
44400 M1 A 1/3/2015
44400 M2 A 1/4/2015
44400 M1 CF 2/1/2105
44400 DG1 CF 2/2/2015
44400 M0 POR 2/11/2015
45000 M0 A 2/1/2015
45000 M1 A 2/3/2015
45000 M2 A 2/4/2015
45000 M1 CF 3/1/2105
45000 DG1 CF 3/2/2015
45000 M0 POR 3/11/2015

，我想动态显示上方的数据，并需要以下面的形式动态显示.

and I want to pivot above data and need in below form dynamically.

现在，我想要以下数据

ID M0_A M1_A M2_A M1_CF DG1_CF M0_POR
44400 1/1/2015 1/3/2015 1/4/2015 2/1/2015 2/2/2015 2/11/2015
45000 2/1/2015 2/3/2015 2/4/2015 3/1/2015 3/2/2015 3/11/2015

非常感谢您的帮助.预先感谢.

I really appreciate your help. Thanks in advance.

推荐答案

对于列tname, ttype中的已定义数量的值对，您可以在下面的查询中使用(请注意，我从示例中更改了列名， 因为您在那里使用了Oracle关键字，所以我也将表命名为tasks，因此您将不得不在代码中的任何地方将此数据更改为实际的列名和表名):

For defined number of pairs of values in columns tname, ttype you can use below query (note that I changed your column names from example, because you used Oracle keywords there, also I named table as tasks, so you will have to change this data to your real column names and table name everywhere in code) :

select * from tasks 
pivot (max(tdate) for (tname, ttype) in 
  (('DG1','CF') DG1_CF, ('M0','A')  M0_A,  ('M0','POR') M0_POR,
   ('M1','A'  ) M1_A,   ('M1','CF') M1_CF, ('M2','A')   M2_A)));

对于动态数量的可能性，您将需要一些过程创建"此查询.在这里，我使用了view. 复制过程代码并进行编译.当表中的数据更改时，您首先必须运行过程，然后只需从过程创建的视图中进行选择. 为了正常运行，您的架构需要先决条件才能创建授予的视图.

For dynamic number of possibilities you will need some procedure "creating" this query. Here I used view for this. Copy procedure code and compile it. When data in your table changes you have to run procedure at first, then simply select from view created by procedure. In order to run properly your schema needs privilleges for creating views granted.

execute create_tasks_view;
select * from v_tasks;

anonymous block completed
   ID DG1_CF     M0_A       M0_POR     M1_A       M1_CF      M2_A     
----- ---------- ---------- ---------- ---------- ---------- ----------
45000 2015-03-02 2015-02-01 2015-03-11 2015-02-03 2015-03-01 2015-02-04 
44400 2015-02-02 2015-01-01 2015-02-11 2015-01-03 2015-02-01 2015-01-04

当然，您可以通过在过程代码中添加或修改order by部分来更改行和列的顺序:

Of course you can change ordering of rows and columns as you wish by adding or modifying order by parts in procedure code:

create or replace procedure create_tasks_view as 
  v_sql varchar2(32767) := '';
begin
  for v in (select distinct tname, ttype from tasks order by tname, ttype) 
  loop
    v_sql := v_sql || '(''' || v.tname || ''',''' || v.ttype || ''') '
      ||v.tname||'_'||v.ttype||',';
  end loop;
  v_sql := 'create or replace view v_tasks as '
    ||'select * from tasks pivot (max(tdate) for (tname, ttype) in ('
    ||rtrim(v_sql, ', ')||'))'; 
  execute immediate v_sql;
end create_tasks_view;

我相信在评论中给您提供的链接中，您的问题还有更通用的解决方案:

I believe there is also more universal solution for your question in link I gave you in comments: Dynamic SQL Pivoting.... It looks very promising, just read carefully section Resources at bottom, and follow the steps of instruction. I didn't check this method personally, but maybe this will suit you more than my "procedure-view" solution.

这篇关于两列在Oracle SQL中枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！