动态sql 行转列

第一次写的存储过程值得留念

create or replace procedure dynamic_sql_duty(
      attendmonth in varchar2,
      sqlsstr out varchar2
) is
CURSOR cur is
         select distinct t.ccid ccid, 
            nvl(lower(u.name),to_char('removed'||t.ccid)) name  
            from cmsuser u,duty t 
            where  t.ccid = u.id(+)
            and to_char( t.tdate ,'YYYY-MM') = attendmonth
            order by t.ccid  ;
 sqls varchar2(8000);

begin

  sqls :='select d.datetime,d.content,c.* from ('||chr(13)
         ||'select '||chr(13);

  FOR cur_result in cur LOOP

   sqls := sqls || '    max( decode( ccid,'||to_char(cur_result.ccid)||', status ) ) '||cur_result.name||' , '||chr(13); 
   sqls := sqls || '    max( decode( ccid,'||to_char(cur_result.ccid)||', id ) ) '||cur_result.name||'id , '||chr(13); 

  END LOOP;

  sqls := sqls 
          ||'    to_char( tdate ,'
          ||''''||'YYYY-MM-DD'||''''||') attenddate , '||chr(13)
          ||'    to_char( tdate ,'||''''||'D'||''''||') todaynum ,'||chr(13)
          ||'    to_char( tdate ,'||''''||'DAY'||''''||') todays ,'||chr(13)
          ||'    tdate '||chr(13)
          ||'  from duty '||chr(13)
          ||'  where '||chr(13)
          ||'    to_char( tdate ,'
          ||''''||'YYYY-MM'||''''||') = '
          ||'''' ||attendmonth||'''' ||chr(13)
          ||'    group by tdate ) c, dutyremark d '||chr(13)
          ||'where  c.tdate = d.datetime(+)';
      --dbms_output.put_line(sqls);
      --execute immediate sqls;
      --open testcor for sqls;
   sqlsstr := sqls;

end dynamic_sql_duty;