declare
sql string(8000);
begin
sql:='select pid,max(case id when '''||id||''' then name else '''' end)'
||' as [''||id||'] from (select distinct id from test) as a'
||' from test group by pid';
end;
sql string(8000);
begin
sql:='select pid,max(case id when '''||id||''' then name else '''' end)'
||' as [''||id||'] from (select distinct id from test) as a'
||' from test group by pid';
end;
create or replace function yzw_sql(
ID varchar2
) return varchar2
is
str_SQL varchar2(8000);
begin
str_SQL := 'select pid,max(decode(id,''' || id || ''',name,''''))'
||' as [' || id ||'] from (select distinct id from test) a'
||' from test group by pid';
return str_SQL;
end;调试:
declare
a varchar2(200);
begin
a := yzw_sql('try');
dbms_output.put_line(a);
end;结果:
select pid,max(decode(id,'try',name,'')) as [try] from (select distinct id from test) a from test group by pid
现有下列记录:
11,AA,100
22, BB,200
33, CC,400
44, DD,500
55, AA,200
66, BB,300
77, EE,800
要得到
ID, AA, BB, CC, DD, EE,合计
11 100 100
22 200 200
33 400 400
44 500 500
55 200 200
66 300 300
77 800 800
请问该语句如何写谢谢