I have a question in Pivoting data in Oracle . Here is my data looks like
我在Oracle中透视数据有一个问题。这是我的数据看起来像
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.
我想在数据上方进行数据转换,并且需要动态地使用以下形式。
Now, I want data as below
现在,我想要数据如下
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.
我非常感谢你的帮助。提前致谢。
1 个解决方案
#1
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) :
对于列tname,ttype中已定义数量的值对,您可以使用下面的查询(请注意,我在示例中更改了列名,因为您在那里使用了Oracle关键字,我也将表命名为任务,因此您必须更改此数据代码中到处的真实列名和表名:
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)));
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
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.
我相信在我给你的评论链接中你的问题也有更普遍的解决方案:动态SQL透视....它看起来很有前途,只需仔细阅读底部的资源部分,并按照指令的步骤。我没有亲自检查这个方法,但也许这比你的“程序视图”解决方案更适合你。
#1
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) :
对于列tname,ttype中已定义数量的值对,您可以使用下面的查询(请注意,我在示例中更改了列名,因为您在那里使用了Oracle关键字,我也将表命名为任务,因此您必须更改此数据代码中到处的真实列名和表名:
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)));
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
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.
我相信在我给你的评论链接中你的问题也有更普遍的解决方案:动态SQL透视....它看起来很有前途,只需仔细阅读底部的资源部分,并按照指令的步骤。我没有亲自检查这个方法,但也许这比你的“程序视图”解决方案更适合你。