转：http://hi.baidu.com/zuoboy/item/011a6f50039f09abadc8578f
接着上一文中的例子来说明，上面静态二维表是针对分类列固定的情况，如果科目是变化的，必须新增了一门 国学 ，那又该如何显示呢，通过修改sql当然可以做到，但如果分类列变化频繁，如何处理呢，这时就需要用到动态交叉表。
动态表要素如下图（此图转载自CSDN）：
以下为生成动态sql的存储过程（oracle10G测试通过）
create or replace procedure create_across_table(tableName IN VARCHAR2,
newColumn IN VARCHAR2,
beforeColumnName IN VARCHAR2,
afterColumnName IN VARCHAR2,
groupColumn IN VARCHAR2,
firstColumnName IN VARCHAR2,
statColumn IN VARCHAR2,
secondColumnName IN VARCHAR2,
operator IN VARCHAR2,
resultSql OUT varchar2
)
AS
/*=================================================================================
创建人：左红亮 2010年12月2日
参数：
tableName --生成交叉表依据的表名
newColumn --生成表头依据的字段名
beforeColumnName --定义动态列开始名称
afterColumnName --定义动态列结束名称
groupColumn --分组依据的字段名
firstColumnName --定义第一列名称
statColumn --欲统计的字段名
secondColumnName --定义第二列名称
operator --统计的运算方式
描述：
其中 tableName,newColumn,groupColumn,statColumn,operator 为必填项
--//=================================================================================*/
--定义参数
v_rssql varchar2(1000); -- 交叉表sql
columnName varchar2(20); --分类列
v_sql varchar2(90); --查询类别
finalOperator varchar2(10); --最后的分组依据，如果传入为空则默认以max运算
TYPE ref_cursor_type IS REF CURSOR;
ctxx ref_cursor_type;
begin
dbms_output.put_line('aaa');
begin
if tableName is not null then
v_rssql := 'SELECT ' || groupColumn;
if firstColumnName is not null then
v_rssql := v_rssql || ' ' || firstColumnName; --第一列的别名
end if;
v_sql := 'SELECT DISTINCT ' || newColumn || ' FROM ' || tableName;
if operator is null then
finalOperator := operator;
else
finalOperator := 'MAX';
end if;
open ctxx for v_sql;
loop
--循环游标
fetch ctxx
into columnName;
exit when ctxx%notfound;
v_rssql := v_rssql || ' ,' || finalOperator || '(CASE ' ||
newColumn || ' WHEN ' || '''' || columnName || '''' ||
' THEN ' || statColumn || ' ELSE Null END ) ';
--给字段起别名
if beforeColumnName is not null then
v_rssql := v_rssql || ' ' || beforeColumnName;
end if;
v_rssql := v_rssql || columnName;
if afterColumnName is not null then
v_rssql := v_rssql || afterColumnName;
end if;
dbms_output.put_line('----------' || columnName);
end loop;
close ctxx;
--拼接结束sql
v_rssql := v_rssql || ' FROM ' || tableName || ' GROUP BY ' ||
groupColumn;
dbms_output.put_line('22' || v_rssql);
resultSql := v_rssql;
end if;
end;
end create_across_table;
测试环境：
1、建表语句：
create table REPORT
(
ID NUMBER,
NAME VARCHAR2(10),
SUBJECT VARCHAR2(10),
GRADE NUMBER
)
tablespace STOCOM
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Add comments to the columns
comment on column REPORT.NAME
is '姓名';
comment on column REPORT.SUBJECT
is '科目';
comment on column REPORT.GRADE
is '成绩';
2、插入测试语句：
insert into REPORT (ID, NAME, SUBJECT, GRADE)
values (1, '张三', '语文', 80);
insert into REPORT (ID, NAME, SUBJECT, GRADE)
values (2, '张三', '数学', 90);
insert into REPORT (ID, NAME, SUBJECT, GRADE)
values (3, '张三', '英语', 70);
insert into REPORT (ID, NAME, SUBJECT, GRADE)
values (4, '李四', '语文', 50);
insert into REPORT (ID, NAME, SUBJECT, GRADE)
values (5, '李四', '数学', 90);
insert into REPORT (ID, NAME, SUBJECT, GRADE)
values (6, '李四', '英语', 60);
insert into REPORT (ID, NAME, SUBJECT, GRADE)
values (7, '王五', '语文', 90);
insert into REPORT (ID, NAME, SUBJECT, GRADE)
values (8, '王五', '数学', 90);
insert into REPORT (ID, NAME, SUBJECT, GRADE)
values (9, '王五', '英语', 90);
3、创建存储过程
4、测试
见下图：sqlsql中测试
resultsql是返回的sql语句，最后结果如下图：
