众所周知，静态SQL的输出结构必须也是静态的。

对于经典的行转列问题，如果行数不定导致输出的列数不定，标准的答案就是使用动态SQL, 到11G里面则有XML结果的PIVOT。

今天在asktom看到的一篇贴子彻底颠覆了我的看法！贴子里的链接指向另一个牛人辈出的荷兰公司：http://technology.amis.nl/2006/0 ... ing-antons-thunder/还记得Anton Scheffer吗？这位神人先是用10G的MODEL写了SUDOKU的一句SQL的解法，在11GR2推出之后又率先用递归WITH写了个只有短短几行的SUDOKU解法。

他的作品还有EXCEL文件生成器。

早在2006年他就发明了真正动态的行转列办法，用的是一系列神秘的函数，如同自定义聚合函数STRAGG里面用的那些。

这个神秘的对象代码如下：1.CREATE OR REPLACE2.type PivotImpl as object3.(4.ret_type anytype,-- The return type of the table function5.stmt varchar2(32767),6.fmt varchar2(32767),7.cur integer,8.static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)',dummy in number := 0 )9.return number,10.static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt invarchar2 := 'upper(@p@)', dummy in number := 0 )11.return number,12.static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)',dummy in number := 0 )13.return number,14.member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )15.return number,16.member function ODCITableClose( self in PivotImpl )17.return number18.)19./20.21.create or replace type body PivotImpl as22.static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)',dummy in number )23.return number24.is25.atyp anytype;26.cur integer;27.numcols number;28.desc_tab dbms_sql.desc_tab2;29.rc sys_refcursor;30.t_c2 varchar2(32767);31.t_fmt varchar2(1000);32.begin33.cur := dbms_sql.open_cursor;34.dbms_sql.parse( cur, p_stmt, dbms_sql.native );35.dbms_sql.describe_columns2( cur, numcols, desc_tab );36.dbms_sql.close_cursor( cur );37.--38.anytype.begincreate( dbms_types.typecode_object, atyp );39.for i in 1 .. numcols - 240.loop41.atyp.addattr( desc_tab( i ).col_name42., case desc_tab( i ).col_type43.when 1then dbms_types.typecode_varchar244.when 2then dbms_types.typecode_number45.when 9then dbms_types.typecode_varchar246.when 11then dbms_types.typecode_varchar2-- show rowid asvarchar247.when 12then dbms_types.typecode_date48.when 208 then dbms_types.typecode_varchar2-- show urowid asvarchar249.when 96then dbms_types.typecode_char50.when 180 then dbms_types.typecode_timestamp51.when 181 then dbms_types.typecode_timestamp_tz52.when 231 then dbms_types.typecode_timestamp_ltz53.when 182 then dbms_types.typecode_interval_ym54.when 183 then dbms_types.typecode_interval_ds55.end56., desc_tab( i ).col_precision57., desc_tab( i ).col_scale58., case desc_tab( i ).col_type59.when 11 then 18-- for rowid col_max_len = 16, and 18 characters areshown60.else desc_tab( i ).col_max_len61.end62., desc_tab( i ).col_charsetid63., desc_tab( i ).col_charsetform64.);65.end loop;66.if instr( p_fmt, '@p@' ) > 067.then68.t_fmt := p_fmt;69.else70.t_fmt := '@p@';71.end if;72.open rc for replace( 'select distinct ' || t_fmt || '73.from( ' || p_stmt || ' )74.order by ' || t_fmt75., '@p@'76., desc_tab( numcols - 1 ).col_name77.);78.loop79.fetch rc into t_c2;80.exit when rc%notfound;81.atyp.addattr( t_c282., case desc_tab( numcols ).col_type83.when 1then dbms_types.typecode_varchar284.when 2then dbms_types.typecode_number85.when 9then dbms_types.typecode_varchar286.when 11then dbms_types.typecode_varchar2-- show rowid asvarchar287.when 12then dbms_types.typecode_date88.when 208 then dbms_types.typecode_urowid89.when 96then dbms_types.typecode_char90.when 180 then dbms_types.typecode_timestamp91.when 181 then dbms_types.typecode_timestamp_tz92.when 231 then dbms_types.typecode_timestamp_ltz93.when 182 then dbms_types.typecode_interval_ym94.when 183 then dbms_types.typecode_interval_ds95.end96., desc_tab( numcols ).col_precision97., desc_tab( numcols ).col_scale98., case desc_tab( numcols ).col_type99.when 11 then 18-- for rowid col_max_len = 16, and 18 characters areshown100.else desc_tab( numcols ).col_max_len101.end102., desc_tab( numcols ).col_charsetid103., desc_tab( numcols ).col_charsetform104.);105.end loop;106.close rc;107.atyp.endcreate;108.anytype.begincreate( dbms_types.typecode_table, rtype );109.rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );110.rtype.endcreate();111.return odciconst.success;112.exception113.when others then114.return odciconst.error;115.end;116.--117.static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )118.return number119.is120.prec pls_integer;121.scale pls_integer;122.len pls_integer;123.csid pls_integer;124.csfrm pls_integer;125.elem_typ anytype;126.aname varchar2(30);127.tc pls_integer;128.begin129.tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );130.--131.if instr( p_fmt, '@p@' ) > 0132.then133.sctx := PivotImpl( elem_typ, p_stmt, p_fmt, null );134.else135.sctx := PivotImpl( elem_typ, p_stmt, '@p@', null );136.end if;137.return odciconst.success;138.end;139.--140.static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number )141.return number142.is143.cur integer;144.numcols number;145.desc_tab dbms_sql.desc_tab2;146.t_stmt varchar2(32767);147.type_code pls_integer;148.prec pls_integer;149.scale pls_integer;150.len pls_integer;151.csid pls_integer;152.csfrm pls_integer;153.schema_name varchar2(30);154.type_name varchar2(30);155.version varchar2(30);156.attr_count pls_integer;157.attr_type anytype;158.attr_name varchar2(100);159.dummy2integer;160.begin161.cur := dbms_sql.open_cursor;162.dbms_sql.parse( cur, p_stmt, dbms_sql.native );163.dbms_sql.describe_columns2( cur, numcols, desc_tab );164.dbms_sql.close_cursor( cur );165.--166.for i in 1 .. numcols - 2167.loop168.t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';169.end loop;170.--171.type_code := sctx.ret_type.getinfo( prec172., scale173., len174., csid175., csfrm176., schema_name177., type_name178., version179., attr_count180.);181.for i in numcols - 1 .. attr_count182.loop183.type_code := sctx.ret_type.getattreleminfo( i184., prec 185., scale 186., len 187., csid 188., csfrm189., attr_type 190., attr_name 191.);192.t_stmt := t_stmt || replace( ', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab( numcols ).col_name || ' ) )'193., '@p@'194., desc_tab( numcols - 1 ).col_name 195.); 196.end loop;197.t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';198.for i in 1 .. numcols - 2199.loop200.if i = 1201.then202.t_stmt := t_stmt || ' group by "' || desc_tab( i ).col_name || '"';203.else204.t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';205.end if;206.end loop;207.--208.--dbms_output.put_line( t_stmt );209.sctx.cur := dbms_sql.open_cursor;210.dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );211.for i in 1 .. attr_count212.loop213.type_code := sctx.ret_type.getattreleminfo( i214., prec 215., scale 216., len217., csid218., csfrm 219., attr_type 220., attr_name 221.);222.case type_code223.when dbms_types.typecode_char then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );224.when dbms_types.typecode_varchar2then dbms_sql.define_column( sctx.cur, i, 'x', 32767 ); 225.when dbms_types.typecode_number then dbms_sql.define_column( sctx.cur, i, cast( null as number ) );226.when dbms_types.typecode_date then dbms_sql.define_column( sctx.cur, i, cast( null as date ) );227.when dbms_types.typecode_urowid then dbms_sql.define_column( sctx.cur, i, cast( null as urowid ) );228.when dbms_types.typecode_timestamp then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp ) );229.when dbms_types.typecode_timestamp_tz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with time zone ) );230.when dbms_types.typecode_timestamp_ltz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with local time zone ) );231.when dbms_types.typecode_interval_ym then dbms_sql.define_column( sctx.cur, i, cast( null as interval year to month ) );232.when dbms_types.typecode_interval_ds then dbms_sql.define_column( sctx.cur, i, cast( null asinterval day to second ) );233.end case;234.end loop;235.dummy2 := dbms_sql.execute( sctx.cur );236.return odciconst.success;237.end;238.--239.member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset ) 240.return number241.is242.c1_col_type pls_integer;243.type_code pls_integer;244.prec pls_integer;245.scale pls_integer;246.len pls_integer;247.csid pls_integer;248.csfrm pls_integer;249.schema_name varchar2(30);250.type_name varchar2(30);251.version varchar2(30);252.attr_count pls_integer;253.attr_type anytype;254.attr_name varchar2(100);255.v1varchar2(32767);256.n1number;257.d1date;258.ur1urowid;259.ids1interval day to second;260.iym1interval year to month;261.ts1timestamp;262.tstz1timestamp with time zone;263.tsltz1 timestamp with local time zone;264.begin265.outset := null;266.if nrows < 1267.then268.-- is this possible???269.return odciconst.success;270.end if;271.--272.--dbms_output.put_line( 'fetch' );273.if dbms_sql.fetch_rows( self.cur ) = 0274.then275.return odciconst.success;276.end if;277.--278.--dbms_output.put_line( 'done' );279.type_code := self.ret_type.getinfo( prec280., scale281., len282., csid283., csfrm284., schema_name285., type_name286., version287., attr_count288.);289.anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );290.outset.addinstance;291.outset.piecewise();292.for i in 1 .. attr_count293.loop294.type_code := self.ret_type.getattreleminfo( i295., prec 296., scale 297., len 298., csid 299., csfrm 300., attr_type 301., attr_name 302.);303.--dbms_output.put_line( attr_name );304.case type_code305.when dbms_types.typecode_char then306.dbms_sql.column_value( self.cur, i, v1 );307.outset.setchar( v1 );308.when dbms_types.typecode_varchar2 then309.dbms_sql.column_value( self.cur, i, v1 );310.outset.setvarchar2( v1 );311.when dbms_types.typecode_number then312.dbms_sql.column_value( self.cur, i, n1 );313.outset.setnumber( n1 );314.when dbms_types.typecode_date then315.dbms_sql.column_value( self.cur, i, d1 );316.outset.setdate( d1 );317.when dbms_types.typecode_urowid then318.dbms_sql.column_value( self.cur, i, ur1 );319.outset.seturowid( ur1 );320.when dbms_types.typecode_interval_ds then321.dbms_sql.column_value( self.cur, i, ids1 );322.323.outset.setintervalds( ids1 );324.when dbms_types.typecode_interval_ym then325.dbms_sql.column_value( self.cur, i, iym1 );326.outset.setintervalym( iym1 );327.when dbms_types.typecode_timestamp then328.dbms_sql.column_value( self.cur, i, ts1 );329.outset.settimestamp( ts1 );330.when dbms_types.typecode_timestamp_tz then331.dbms_sql.column_value( self.cur, i, tstz1 );332.outset.settimestamptz( tstz1 );333.when dbms_types.typecode_timestamp_ltz then334.dbms_sql.column_value( self.cur, i, tsltz1 );335.outset.settimestampltz( tsltz1 );336.end case;337.end loop;338.outset.endcreate;339.return odciconst.success;340.end;341.--342.member function ODCITableClose( self in PivotImpl )343.return number344.is345. c integer;346.begin347. c := self.cur;348.dbms_sql.close_cursor( c );349.return odciconst.success;350.end;351.end;352./353.354.-- 在外面包装一层PLSQL函数：355.create or replace356.function pivot( p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in number := 0 ) 357.return anydataset pipelined using PivotImpl;358./下面就以SCOTT.EMP表为例子，这个表的结构为：Name Null?Type------------------ ----------------EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9)MGR NUMBER(4)HIREDATE DATESAL NUMBER(7,2)COMM NUMBER(7,2)DEPTNO NUMBER(2)如果要列出每个部门里每种职位的平均工资，传统的写法是这样：select deptno, avg(DECODE(job,'ANALYST',sal))AS ANALYST, avg(DECODE(job,'CLERK',sal))AS CLERK, avg(DECODE(job,'MANAGER',sal))AS MANAGER, avg(DECODE(job,'PRESIDENT',sal)) AS PRESIDENT, avg(DECODE(job,'SALESMAN',sal))AS SALESMANfrom scott.empgroup by deptno;输出：DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN------- ---------- ---------- ---------- ---------- ----------3095028501400203000950297510130024505000用这个神奇的pivot函数的写法：select *from table( pivot('select deptno,job,avg(sal) sal_avgfrom scott.empgroupby deptno,job'));输出：DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN ---------- ---------- ---------- ---------- ---------- ----------3095028501400203000950297510130024505000。