Example: Pivot Operation on View - Analytics Database - Teradata Vantage

The following example of a view as a PIVOT source.

Assume a view, v1, is defined on the table s1:

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

sel * from s1;

sel * from s1;

Result:

 *** 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;

Result:

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

sel * from v1;

select * from v1;

Result:

 *** 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

The following query generates sales report with respect to each year on view V1:

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

Result:

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

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