Oracle动态sql行转列

#odata1 为临时表

#data 为报表展示临时表

@InsertItem@ 为插入的列名，例如：F_103444,F_103445,F_103446,F_103447,F_103448,

代码如下：

DECLARE

V_Sql VARCHAR2(8000);
CURSOR CURSOR_1 IS select distinct FASSETTYPEGROUPID from #odata1;
BEGIN
V_Sql := 'INSERT INTO #data(FIDENTITYID,FDEPTNAME,@InsertItem@FDEPRSUM)';
V_Sql := V_Sql || 'select ROW_NUMBER() OVER(order by FDEPTNAME) FIDENTITYID,FDEPTNAME FDEPTNAME';

FOR V_XCLCK IN CURSOR_1
LOOP

V_Sql := V_Sql || ' , max(case FASSETTYPEGROUPID when ''' || V_XCLCK.FASSETTYPEGROUPID || ''' then FDRFUND else 0 end) [F_' || V_XCLCK.FASSETTYPEGROUPID || ']';

END LOOP;

V_Sql := V_Sql || ' , sum(FDRFUND) FDEPRSUM from #odata1 group by FDEPTNAME';

EXECUTE IMMEDIATE V_Sql;

--dbms_output.put_line(V_Sql);
END;