竖表转横表(ORACLE)

　　
竖表转横表(ORACLE)一般形式为将一个列为C!, C2, … CN的表，以C1, C2, … CX为基准，将CX+1, … CN的不同值改为列。一般化的语法：
　　SELECT C1, C2, … CX,..... 另外,如果无基准的情况 比如.....见下文[@more@]

一般形式为将一个列为C!, C2, … CN的表，以C1, C2, … CX为基准，将CX+1, … CN的不同值改为列。一般化的语法：
　　SELECT C1, C2, … CX,
　　
　　MAX(DECODE(rn, 1, CX+1, NULL)) CX+1_1, … MAX(DECODE(rn, 1, CN, NULL))
　　CN_1
　　
　　MAX(DECODE(rn, 2, CX+1, NULL)) CX+1_2, … MAX(DECODE(rn, 2, CN, NULL))
　　CN_2
　　
　　…
　　
　　MAX(DECODE(rn,N,CX+1, NULL)) CX+1_N, … MAX(DECODE(rn,N, CN, NULL))
　　CN_N
　　FROM
　　(SELECT C1, C2, …
　　CN,
　　ROW_NUMBER() OVER (PARTITION
　　BY C1, C2, … CX ORDER BY ) rn
　　
　　FROM T
　　
　　WHERE …)
　　GROUP BY C1, C2, … CX;
　　
　　通用包：
　　CREATE OR REPLACE PACKAGE
　　pkg_pivot
　　AS
　　
　　TYPE refcursor IS REF CURSOR;
　　
　　TYPE ARRAY IS TABLE OF VARCHAR2(30);
　　
　　PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT
　　NULL,
　　 p_max_cols_query IN VARCHAR2
　　DEFAULT NULL,
　　 p_query IN
　　VARCHAR2,
　　 p_anchor IN
　　ARRAY,
　　 p_pivot IN
　　ARRAY,
　　 p_cursor IN OUT
　　refcursor);
　　END;
　　
　　CREATE OR REPLACE PACKAGE BODY
　　pkg_pivot
　　AS
　　
　　PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT
　　NULL,
　　 p_max_cols_query IN VARCHAR2
　　DEFAULT NULL,
　　 p_query IN
　　VARCHAR2,
　　 p_anchor IN
　　ARRAY,
　　 p_pivot IN
　　ARRAY,
　　 p_cursor IN OUT
　　refcursor)
　　
　　AS
　　
　　l_max_cols NUMBER;
　　
　　l_query LONG;
　　
　　l_cnames ARRAY;
　　
　　BEGIN
　　
　　IF (p_max_cols IS NOT NULL)
　　
　　THEN
　　 EXECUTE IMMEDIATE p_max_cols_query INTO
　　l_max_cols;
　　
　　ELSE
　　 RAISE_APPLICATION_ERROR(-20001, 'Cannot
　　figure out max cols');
　　
　　END IF;
　　
　　
　　l_query := 'select ';
　　
　　FOR i IN 1 .. p_anchor.count
　　
　　LOOP
　　 l_query := l_query || p_anchor(i) ||
　　',';
　　
　　END LOOP;
　　
　　
　　FOR i IN 1 .. l_max_cols
　　
　　LOOP
　　 FOR j IN 1 ..
　　p_pivot.count
　　 LOOP
　　 l_query := l_query ||
　　'max(decode(rn,'||i||','||p_pivot(j)||',null)) '||p_pivot(j) || '_' || i ||
　　',';
　　 END LOOP;
　　
　　END LOOP;
　　
　　
　　l_query := RTRIM(l_query,',') || ' from (' || p_query || ') group by
　　';
　　
　　FOR i IN 1 .. p_anchor.count
　　
　　LOOP
　　 l_query := l_query || p_anchor(i) ||
　　',';
　　
　　END LOOP;
　　
　　l_query := RTRIM(l_query,',');
　　
　　
　　EXECUTE IMMEDIATE 'alter session set
　　cursor_sharing=force';
　　
　　OPEN p_cursor FOR l_query;
　　
　　EXECUTE IMMEDIATE 'alter session set
　　cursor_sharing=exact';
　　
　　END;
　　END;
　　其中：
　　p_max_cols_query为SELECT MAX(COUNT(*)) FROM TABLE_NAME GROUP BY C1, C2, …
　　CX;
　　p_query为SELECT C1, C2, … CN ROW_NUMBER() OVER (PARTITION BY C1, C2, … CX
　　ORDER BY ) rn FROM TABLE_NAME;
　　p_anchor为pkg_pivot.array(C1, C2, … CX)
　　p_pivot为pkg_pivot.array(CX+1, CX+2, … CN)
　　p_cursor为返回的游标。

另外,如果无基准的情况 比如

表结构与信息如下：
CREATE TABLE TB_ITEM_INFO(
item_name varchar2(20),
item_data number(10,4),
constraint PK_ITEM_INFO primary key (item_name)
);

item_name(primary_key) item_data
value1 1.0
value2 2.0
value3 3.0

欲转为：
value1 value2 value3
1.0 2.0 3.0

则转化函数为：

SELECT SUM(decode(item_name, 'value1', item_data, 0)) value1, SUM(decode(item_name, 'value2', item_data, 0)) value2, SUM(decode(item_name, 'value3', item_data, 0)) value3 FROM TB_ITEM_INFO;

（即 把group by 语句去掉即可）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8780503/viewspace-1033007/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8780503/viewspace-1033007/