ITPub博客

首页 > 应用开发 > IT综合 > how to get an pivat table

how to get an pivat table

原创 IT综合 作者:jametong 时间:2005-03-29 23:17:44 0 删除 编辑

1. use dual table and connect by . ( 转载自 hotsos )
SQL> @aaa.sql
SQL> with x as (
  2           select trunc(sysdate,'YEAR') + rownum -1
  3           from dual
  4           connect by 1 = 1
  5           and level <= add_months(trunc(sysdate,'YEAR'),12) - trunc(sysdate,'YEAR')
  6  )
  7  select * from x
  8  /

TRUNC(SYSD                                                                      
----------                                                                      
2005-01-01                                                                      
2005-01-02                                                                      
2005-01-03                                                                      
2005-01-04                                                                      
2005-01-05                                                                      
2005-01-06                                                                      
2005-01-07                                                                      
2005-01-08                                                                      
2005-01-09                                                                      
2005-01-10                                                                      
2005-01-11                                                                      
.....


2. 使用Oracle的数据字典表. 如果需要的技术很大, 可以使用两个数据字典表进行笛卡儿连接.:-) ( 转载自 asktom )
SQL> @aaa.sql
SQL> with x as (
  2           select trunc(sysdate,'YEAR') + rownum -1
  3           from dba_objects
  4           where rownum <= add_months(trunc(sysdate,'YEAR'),12) - trunc(sysdate,'YEAR')
  5  )
  6  select * from x
  7  /

TRUNC(SYSD                                                                      
----------                                                                      
2005-01-01                                                                      
2005-01-02                                                                      
2005-01-03                                                                      
2005-01-04                                                                      
2005-01-05                                                                      
2005-01-06                                                                      
2005-01-07                                                                      
2005-01-08                                                                      
2005-01-09                                                                      
2005-01-10                                                                      
2005-01-11                                                                      
.....

3. 使用Oracle9i的pipelined function来进行处理:-) (转载自 Jonathan Gennick).
create TYPE pivot_row AS OBJECT (
   x NUMBER
)
/
TYPE pivot_table
   AS TABLE OF pivot_row
/
PACKAGE pivot_package AS
FUNCTION pivot (num_rows IN NUMBER)
   RETURN pivot_table
   PARALLEL_ENABLE PIPELINED;
END;
/

PACKAGE BODY pivot_package AS
   FUNCTION pivot (num_rows IN NUMBER)
   RETURN pivot_table PARALLEL_ENABLE PIPELINED IS
         outrow pivot_row := pivot_row (0);
      BEGIN
         FOR x IN 0..num_rows-1 LOOP
            outrow.x := x;
            PIPE ROW(outrow);

         END LOOP;
         RETURN;
      END;
END;
/
SQL> @aaa.sql
SQL> with x as (
  2           select trunc(sysdate,'YEAR') + rownum -1
  3           from table(pivot_package.pivot(add_months(trunc(sysdate,'YEAR'),12) - trunc(sysdate,'YEAR')))
  4  )
  5  select * from x
  6  /

TRUNC(SYSD                                                                      
----------                                                                      
2005-01-01                                                                      
2005-01-02                                                                      
2005-01-03                                                                      
2005-01-04                                                                      
2005-01-05                                                                      
2005-01-06                                                                      
2005-01-07                                                                      
2005-01-08                                                                      
2005-01-09                                                                      
2005-01-10                                                                      
2005-01-11                                                                      
.........


[@more@]

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

上一篇: 由ora-1652想到的.
请登录后发表评论 登录
全部评论

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    280599