how to get an pivat table

最新推荐文章于 2024-10-11 17:04:33 发布

congticuan8801

最新推荐文章于 2024-10-11 17:04:33 发布

阅读量74

点赞数

文章标签：数据库

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

转载于:http://blog.itpub.net/94317/viewspace-794350/