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.
If you don't have Teradata environment available to use, try installing one using VMware:
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:
For more details about the syntax for these two clauses, refer to official documentation: