行转列-Turning Rows Into Columns
七月 24, 2008 by 枯荣长老
行转列的情况，在数据库的应用中经常出现。比如要实现将员工工资按月展现，或者产品的销售额按月来展现或者统计排序。实现的方法有很多，在这里根据asktom上的文章，演示一种比较简洁的做法。展现的功能是按照员工id来展现每个月的工资额度。
CREATE TABLE T
(
EMP_ID NUMBER,
MONTH NUMBER,
SALARY NUMBER
);
CREATE OR REPLACE PACKAGE pivot
AS
TYPE rc IS REF CURSOR;
PROCEDURE DATA (p_cursor IN OUT rc);
END;
/
CREATE OR REPLACE PACKAGE BODY pivot
AS
PROCEDURE DATA (p_cursor IN OUT rc)
IS
l_stmt LONG;
BEGIN
l_stmt := 'select emp_id';
FOR x IN (SELECT DISTINCT MONTH
FROM t
ORDER BY 1)
LOOP
l_stmt := l_stmt || ', max(decode(month,'
|| x.MONTH || ', salary)) as month_'
|| x.MONTH;
END LOOP;
l_stmt := l_stmt || ' from t group by emp_id order by 1';
OPEN p_cursor FOR l_stmt;
END;
END;
/
VARIABLE x refcursor
SET autoprint on
EXEC pivot.data( :x );
测试结果：
测试：
SQL> select * from t;
EMP_ID MONTH SALARY
---------- ---------- ----------
1 1 10
1 2 20
1 3 30
2 4 100
2 8 101
SQL> VARIABLE x refcursor
SQL> SET autoprint on
SQL> EXEC pivot.data( :x );
PL/SQL procedure successfully completed.
EMP_ID MONTH_1 MONTH_2 MONTH_3 MONTH_4 MONTH_8
---------- ---------- ---------- ---------- ---------- ----------
1 10 20 30
2 100 101
如果将MAX改成SUM，则：
SQL> select * from t order by emp_id,month;
EMP_ID MONTH SALARY
---------- ---------- ----------
1 1 10
1 1 10000
1 2 20
1 3 30
2 4 100
2 8 101
SQL> VARIABLE x refcursor
SQL> SET autoprint on
SQL> EXEC pivot.data( :x );
PL/SQL procedure successfully completed.
EMP_ID MONTH_1 MONTH_2 MONTH_3 MONTH_4 MONTH_8
---------- ---------- ---------- ---------- ---------- ----------
1 10010 20 30
2 100 101
References:
Raj -- Thanks for the question regarding "Transforming row to columns", version Oracle 8i
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:419593546543
Su -- Thanks for the question regarding "Turning Column Into Rows", version 9.2.0
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:31263576751669
G -- Thanks for the question regarding "Rows into columns", version 9.0.1
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15151874723724