ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 行转列-Turning Rows Into Columns

行转列-Turning Rows Into Columns

原创 Linux操作系统 作者:dvlue 时间:2009-03-18 14:54:27 0 删除 编辑
 

行转列-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

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

请登录后发表评论 登录
全部评论

注册时间:2008-06-26

  • 博文量
    45
  • 访问量
    99382