行转列-Turning Rows Into Columns

 

行转列-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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7656893/viewspace-571418/