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 categoryand uses SUMand COUNTaggregate 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
;
The output is not quite what we want due to column id:
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: