languageEnglish

Teradata: PIVOT and UNPIVOT Clause

visibility 96 comment 0 access_time 2 months ago

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

info Last modified by Raymond 2 months ago copyright This page is subject to Site terms.

Subscribe newsletter

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext

visibility 1928
thumb_up 0
access_time 2 years ago
visibility 2308
thumb_up 0
access_time 10 months ago
visibility 312
thumb_up 0
access_time 2 years ago