例: ビューのピボット操作 - Teradata Database - Teradata Vantage NewSQL Engine - PIVOT関数の例。

ピボット ソースとしてのビューの例は以下のとおりです。

v1ビューがテーブルs1で定義されると仮定します。

CREATE TABLE s1(yr INTEGER, mon VARCHAR(4), sales INTEGER);

sel * from s1;

sel * from s1;

 *** Query completed. 8 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

         yr  mon         sales
-----------  ----  -----------
       2001  jan           100
       2003  jan           300
       2002  jan           150
       2001  feb           110
       2003  feb           310
       2002  feb           200
       2001  mar           120
       2002  mar           250

CREATE VIEW V1 AS select yr,sales  from s1;

 *** View has been created.
 *** Total elapsed time was 1 second.


sel * from v1;

select * from v1;

 *** Query completed. 8 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

         yr        sales
-----------  -----------
       2002          150
       2003          300
       2002          200
       2003          310
       2002          250
       2001          100
       2001          110
       2001          120

次のクエリーは、ビューV1に各年の販売レポートを生成します。

SELECT *
FROM v1 PIVOT (SUM(sales) FOR yr IN (2001,2002,2003)) tmp;


 *** Query completed. One row found. 3 columns returned.
 *** Total elapsed time was 1 second.

       2001         2002         2003
-----------  -----------  -----------
        330          600          610