Tarzan erős jeligére:
create table tabla (
ID number,
month number,
country char(2),
ertek number);
insert into tabla (ID, month, country, ertek)
values (1, 1, 'HU', 100);
insert into tabla (ID, month, country, ertek)
values (1, 2, 'HU', 200);
insert into tabla (ID, month, country, ertek)
values (1, 3, 'HU', 300);
insert into tabla (ID, month, country, ertek)
values (1, 1, 'AT', 50);
insert into tabla (ID, month, country, ertek)
values (1, 3, 'AT', 500);
insert into tabla (ID, month, country, ertek)
values (1, 1, 'DE', 100);
insert into tabla (ID, month, country, ertek)
values (1, 2, 'DE', 1000);
commit;
declare
v_orszagkod varchar2(4000);
v_sql varchar2(4000);
begin
select listagg('''' || country || '''', ', ')
within group (order by country)
into v_orszagkod
from (select distinct country from tabla);
--dbms_output.put_line(v_orszagkod);
v_sql := 'create table pivot_table as' || chr(13) || chr(10) ||
'select *' || chr(13) || chr(10) ||
'from tabla' || chr(13) || chr(10) ||
'PIVOT (' || chr(13) || chr(10) ||
' SUM(ertek)' || chr(13) || chr(10) ||
' FOR country' || chr(13) || chr(10) ||
' IN (' || v_orszagkod || ')' || chr(13) || chr(10) ||
')' || chr(13) || chr(10) ||
'where ID = 1' || chr(13) || chr(10) ||
'order by month';
--dbms_output.put_line(v_sql);
execute immediate v_sql;
end;
select *
from pivot_table;
[ Szerkesztve ]
Hello IT! Have you tried turning it off and on again?