Example: The PIVOT Query Response in Different Response Modes - Teradata Vantage - Analytics Database

Assume a table t1 is defined as:

CREATE TABLE t1(yr INTEGER,mon VARCHAR(3),sales INTEGER);

Assume that following insert statements 
INSERT t1 VALUES(2003,'Jan',300);
INSERT t1 VALUES(2001,'Jan',100);
INSERT t1 VALUES(2003,'Feb',310);
INSERT t1 VALUES(2001,'Feb',110);
INSERT t1 VALUES(2002,'Jan',150);
INSERT t1 VALUES(2001,'Mar',120);
INSERT t1 VALUES(2002,'Feb',200);
INSERT t1 VALUES(2002,'Mar',250);
INSERT t1 VALUES(2003,'Mar',1000);

Assuming that the PIVOT query is submitted for execution, the output returns as different responses modes.

For a PIVOT query:

SELECT * FROM t1 PIVOT(SUM(sales) FOR mon IN ('Jan','Feb','Mar')) tmp;

For a PIVOT query re-written as a SELECT statement using CASE expressions:

SELECT yr,SUM(case when mon='Jan' then sales end) AS "Jan",
SUM(case when mon='Feb' then sales end) AS "Feb",
SUM(case when mon='Mar' then sales end) AS "Mar"
FROM t1 GROUP BY yr;

Result:

.field mode

 *** Query completed.  3 rows found.

         yr          Jan          Feb          Mar
-----------  -----------  -----------  -----------
       2001          100          110          120
       2003          300          310         1000
       2002          150          200          250

.multipartrecord mode

 *** Query completed.  3 rows found.

        yr         Jan         Feb         Mar
----------  ----------  ----------  ----------
      2001         100         110         120
      2003         300         310        1000
      2002         150         200         250


.record mode

 *** Query completed.  3 rows found.

         yr          Jan          Feb          Mar
-----------  -----------  -----------  -----------
       2001          100          110          120
       2003          300          310         1000
       2002          150          200          250

.indicator mode

 *** Query completed.  3 rows found.

         yr          Jan          Feb          Mar
-----------  -----------  -----------  -----------
       2001          100          110          120
       2003          300          310         1000
       2002          150          200          250