SQLSERVER :行列转换例子： http://www.cnblogs.com/gaizai/p/3753296.html#_labelFour
1. Oracle 11g之后新增了行列转换的函数 pivot 和 unpivot 大大简化了行列转换处理。
2. 在Oracle 10g及以前版本，一般是通过各种SQL进行行列转换，列入下面例子：
create or replace procedure P_row_to_col(tabname in varchar2, group_col in varchar2, column_col in varchar2, value_col in varchar2, Aggregate_func in varchar2 default 'max', colorder in varchar2 default null, roworder in varchar2 default null, when_value_null in varchar2 default null, viewname in varchar2 default 'v_tmp') Authid Current_User as sqlstr varchar2(2000) := 'create or replace view ' || viewname || ' as select ' || group_col || ' '; c1 sys_refcursor; v1 varchar2(100);beginopen c1 for 'select distinct ' || column_col || ' from ' || tabname || case when colorder isnot null then ' order by ' || colorderend; loop fetch c1 into v1; exit when c1%notfound; sqlstr := sqlstr || chr(10) || ',' || case when when_value_null isnot null then 'nvl('end || Aggregate_func || '(decode(to_char(' || column_col || '),''' || v1 || ''',' || value_col || '))' || case when when_value_null isnot null then chr(44) || when_value_null || chr(41)end || '"' || v1 || '"';end loop; close c1; sqlstr := sqlstr || ' from ' || tabname || ' group by ' || group_col || case when roworder isnot null then ' order by ' || roworderend; execute immediate sqlstr;end P_row_to_col;--测试数据 create table rowtocol_test as select 2009 year,1 month,'AAA1' dept,50000 expenditure from dual union all select 2009,2,'AAA1',20000 from dual union all select 2009,2,'AAA1',30000 from dual union all select 2010,1,'AAA1',35000 from dual union all select 2009,2,'BBB2',40000 from dual union all select 2009,3,'BBB2',25000 from dual union all select 2010,2,'DDD3',60000 from dual union all select 2009,2,'DDD3',15000 from dual union all select 2009,2,'DDD3',10000 from dual; select * from rowtocol_test; --执行测试 begin p_row_to_col('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0') ; end; select * from v_tmp; ================================================================================================================
例子二 三：
--测试数据 create table t (XH varchar2(10), DDATE date, SXF int); insert into t select 1,sysdate,10 from dual union all select 1,sysdate+1,14 from dual union allselect 1,sysdate+2,23 from dual union allselect 2,sysdate,21 from dual union allselect 2,sysdate+1,24 from dual union allselect 3,sysdate,13 from dual union allselect 3,sysdate+1,22 from dual; -- create or replace package sp_test istype ResultData is ref cursor; procedure getRstData( rst out ResultData); end sp_test; / create or replace package body sp_test isprocedure getRstData( rst out ResultData) is begin declare cursor cur is select distinct (DDATE) from t; tmp_ddate date; str varchar2(4000); beginstr:='select xh'; open cur; loop fetch cur into tmp_ddate; exit when cur%notfound; str:=str||',sum(decode(to_char(ddate,''yyyymmdd''),'||chr(39)||to_char(tmp_ddate,'yyyymmdd')||chr(39)||',sxf,0)) "'||to_char(tmp_ddate,'yyyymmdd')||'"'; end loop; str:=str||' from t group by xh'; -- dbms_output.put_line(str); close cur; open rst for str; end; end; end sp_test; / --输出结果 1 10 14 23 2 21 24 0 3 13 22 0 ======================== 例子三： ------------建表 CREATE TABLE TEST(WL VARCHAR2(10),XYSL INTEGER,XYCK VARCHAR2(10),XCLCK VARCHAR2(10),XCLCKSL INTEGER,PC INTEGER); ------------ 第一部分测试数据 INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123); INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111); INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222); INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223); COMMIT; -------------------- 动态生成结果表 DECLARE V_SQL VARCHAR2(2000); CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK; BEGIN V_SQL := 'SELECT WL,XYSL,XYCK'; FOR V_XCLCK IN CURSOR_1 LOOP V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK || ''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK; END LOOP; V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK'; --DBMS_OUTPUT.PUT_LINE(V_SQL); V_SQL := 'CREATE TABLE RESULT AS '|| V_SQL; --DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL; END; --------------- 结果 SELECT * FROM RESULT T;