Teradata Convert Rows To Columns – Pivot Rows To Columns – Cool And Easy 3 Example Of Usage!

You are currently viewing Teradata Convert Rows To Columns – Pivot Rows To Columns – Cool And Easy 3 Example Of Usage!
Share This Post, Help Others, And Earn My Heartfelt Appreciation! :)
4.9
(589)

Sometimes you got business requirements to create new view or dashboard and to do this you [ Teradata convert rows to columns ] need to convert rows to columns. You can transpose them on two ways: using CASE or PIVOT function. In this post I will show you how to convert rows to columns at Teradata on these two ways.

Teradata Convert Rows To Columns

To explain you how to use PIVOT function to pivot rows to columns, first we create PRODUCT table and insert some samples records in it.

CREATE TABLE TUTORIAL.PRODUCT (
	PRODUCER_NME VARCHAR(100),
	PRODUCT_NME	VARCHAR(100),
	R_YR	INTEGER,
	R_MTH	BYTEINT,
	SOLD_NO NUMBER(7,2)
	);

And this is the data preview:

Teradata convert rows to columns - Pivot rows to columns - cool and easy 3 example of usage!

PIVOT Function In Teradata

PIVOT function appeared in the 16 version of Teradata database and allow us quickly tranpose rows into columns. Let’s look at the example below!

SEL *
FROM TUTORIAL.PRODUCT 
PIVOT (
	SUM(SOLD_NO)
	FOR R_MTH IN (
		1 AS MTH_1, 
		2 AS MTH_2, 
		3 AS MTH_3)
) piv;

R_MTH in my table is the atribute which I want to see as columns, but as value for this attributes I want to see sum of the SOLD_NO values. I can write SEL *, because my table not contain any additional attributes – in case when your table have a lot of columns, probably you will must limit them.

The screenshot below presents like my data look after using the PIVOT function. (Teradata convert rows to columns)

Teradata convert rows to columns - Pivot rows to columns - cool and easy 3 example of usage!

CASE Statement

You can get the same results as using the pivot function using the CASE statment. CASE statment is used more often than PIVOT so you can feel more comfortable using it. But remember what is the better, quicker and shorter depends how many attributes, calculation and conditions you need to use. And in most cases the PIVOT function wins 🙂

SEL 
	PRODUCER_NME, 
	PRODUCT_NME, 
	R_YR,
	SUM(CASE WHEN R_MTH = 1 THEN SOLD_NO ELSE NULL END) AS MTH_1,
	SUM(CASE WHEN R_MTH = 2 THEN SOLD_NO ELSE NULL END) AS MTH_2,
	SUM(CASE WHEN R_MTH = 3 THEN SOLD_NO ELSE NULL END) AS MTH_3
FROM TUTORIAL.PRODUCT 
GROUP BY 
	PRODUCER_NME, 
	PRODUCT_NME, 
	R_YR;

The SELECT statment above can give you the same result like the PIVOT function.

Scripts

If you want to download all the scripts using in this post, you can download the Product.txt file below:

If you enjoyed this post please add the comment below and share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!

How useful was this post?

Click on a star to rate it!

Average rating 4.9 / 5. Vote count: 589

No votes so far! Be the first to rate this post.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments