行转列与列转行案例.docVIP

1.使用视图 SQL code? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 createorreplaceprocedurerow_to_col(tabnameinvarchar2, group_colinvarchar2, column_colinvarchar2, value_colinvarchar2, Aggregate_funcinvarchar2defaultmax, colorderinvarchar2defaultnull, roworderinvarchar2defaultnull, when_value_nullinvarchar2defaultnull, viewnameinvarchar2defaultv_tmp) AuthidCurrent_User as sqlstr varchar2(2000):=create or replace view ||viewname|| as select ||group_col|| ; c1 sys_refcursor; v1 varchar2(100); begin openc1forselect distinct ||column_col|| from ||tabname||casewhencolorderisnotnullthen order by ||colorderend; loop fetchc1intov1; exitwhenc1%notfound; sqlstr:=sqlstr||chr(10)||,||casewhenwhen_value_nullisnotnullthennvl(end|| Aggregate_func||(decode(to_char(||column_col||),||v1||,||value_col||))|| casewhenwhen_value_nullisnotnullthenchr(44) ||when_value_null||chr(41)end||||v1||; endloop; closec1; sqlstr:=sqlstr|| from ||tabname|| group by ||group_col||casewhenroworderisnotnullthen order by ||roworderend; executeimmediate sqlstr; endrow_to_col; 这里修改了传入参数名，使其更容易理解。继续使用了创建视图这个方法，当然也可以改成用游标传出。参数：tabname 需要进行行转列操作的表名；group_col 查询结果要按某列或某些列分组的字段名；column_col 要从行转成列的字段；value_col 需要聚合的值字段；Aggregate_func 选用的聚合函数，可选，默认为max；colorder 行转列后列的排序，可选；roworder 行转列后记录的排序，可选；when_value_null 若value_col字段的值聚合后为空，则转换成该值，可选；viewname 创建的视图名称，可选，默认为v_tmp。举例： SQL code? 1 2 3 4 5 6 7 8 9 10 11 --测试数据 createtablerowtocol_testas select2009year,1month,部门1dept,50000 expenditurefromdual unionallselect2009,2,部门1,20000fromdual unionallselect2009,2,部门1,30000fromdual unionallselect2010,1,部门1,35000fromdual unionallselect2009,2,部门2,40000fromdual unionallselect2009,3,部门2,25000fromdual unionallselect2010,2,部门3,60000fromdual unionallselect2009,2,部门3,15000fromdual unionallselect2009,2,部门3,10000fromdual; 我现在想根据year和month分组，将部门转成列。 SQL code? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 SQLselect*fromrowtocol_test; YEARMONTHDEPT EXPENDITURE -----