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)));
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.