Teradata: PIVOT and UNPIVOT Clause

PIVOT and UNPIVOT are used to transform rows to columns and columns to rows. Without these two clauses, we traditionally use GROUP BY and CASE WHEN clauses to implement similar function. Since Teradata version 16.00, these two clauses are supported. This article shows two simple examples about using these two clauses.

Example environment

If you don't have Teradata environment available to use, try installing one using VMware:

Install Teradata Express 15.0.0.8 by Using VMware Player 6.0 in Windows - Kontext

In this article, I will use a table named TestDb.test_table. It was created using the following statement:

CREATE SET TABLE TestDb.test_table ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      id INTEGER NOT NULL,
      category VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      amount DECIMAL(5,0))
PRIMARY INDEX ( id );

There are 9 sample records in the table.

id category amount

1 1 A 10

2 2 A 11

3 3 A 12

4 4 B 100

5 4 C 1,000

6 5 B 101

7 5 C 1,001

8 6 B 102

9 6 C 1,002

Use PIVOT clause

PIVOT clause is used to transform rows to columns. The following statement pivots the table on column category and uses SUM and COUNT aggregate function to summarize data.

SELECT  *
FROM    TestDb.test_table
PIVOT (SUM(amount) AS AMT, COUNT(*) AS CNT 
        FOR category IN ('A' AS CA, 'B' AS CB, 'C' AS CC)
       ) AS PVT
;

To fix it, we can just include the required columns in a subquery:

SELECT  *
FROM    (SELECT category, amount FROM TestDb.test_table) AS T
PIVOT (SUM(amount) AS AMT, COUNT(*) AS CNT 
        FOR category IN ('A' AS CA, 'B' AS CB, 'C' AS CC)
       ) AS PVT
;

Now the result looks like the following screenshot:

Use UNPIVOT clause

UNPIVOT does the opposite of PIVOT. It transforms columns to rows. The following statement unpivot the pivoted result set back to rows.

WITH PVT AS
(SELECT *
FROM    (SELECT category, amount FROM TestDb.test_table) AS T
PIVOT (SUM(amount) AS AMT, COUNT(*) AS CNT 
        FOR category IN ('A' AS CA, 'B' AS CB, 'C' AS CC)
       ) AS TMP
)
SEL * FROM PVT
UNPIVOT INCLUDE NULLS ( (amount,cnt) for category in 
            (   (CA_AMT,CA_CNT) AS 'A',
                (CB_AMT,CB_CNT) AS 'B',
                (CC_AMT,CC_CNT) AS 'C'
            )
        ) TMP
;

The output looks like the following screenshot:

References

For more details about the syntax for these two clauses, refer to official documentation:

• PIVOT • Teradata Database SQL Functions, Operators, Expressions, and Predicates
  
• UNPIVOT • Teradata Database SQL Functions, Operators, Expressions, and Predicates