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.

## Table of Contents

## Introduction

The PIVOT operator in Teradata is used to rotate rows of data into columns, or vice versa. This can be useful for presenting data in a more organized or meaningful way, or for enabling faster or easier analysis of the data.

The syntax for the PIVOT function varies depending on the specific database management system (DBMS) being used, but generally it follows the form:

SELECT * FROM ( SELECT column1, column2, ..., columnN FROM table WHERE condition ) PIVOT ( aggregate_function(column_to_aggregate) FOR column_to_pivot IN (values_to_pivot) )

In this syntax:

`SELECT column1, column2, ..., columnN`

specifies the columns to be included in the pivot table.`FROM table`

specifies the source table for the data.`WHERE condition`

is an optional clause that can be used to filter the data.`aggregate_function(column_to_aggregate)`

specifies the function to be used to aggregate the data in`column_to_aggregate`

. Examples of aggregate functions include`SUM`

,`AVG`

,`MAX`

, and`MIN`

.`FOR column_to_pivot IN (values_to_pivot)`

specifies the column to be pivoted and the values to be used as the pivoted columns.

The PIVOT function is often used in conjunction with the GROUP BY clause to group the data by one or more columns before pivoting. It can also be used with the ORDER BY clause to specify the order in which the pivoted columns should be displayed.

Some examples of when you might use the PIVOT function include:

- Summarizing sales data by product, with each product as a pivoted column
- Displaying customer data by month, with each month as a pivoted column
- Transforming a table of event logs into a table of counts by event type, with each event type as a pivoted column

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

**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)*

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

## Teradata Pivot Function – Additional Example

Here is an example of how to use The PIVOT operator in Teradata:

SELECT * FROM ( SELECT department, salary, year FROM employees ) PIVOT ( SUM(salary) FOR year IN (2013, 2014, 2015) )

This example selects data from the `employees`

table and pivots the data so that the `salary`

values are summed for each year and displayed as columns. The resulting table would have a column for each year (2013, 2014, 2015), with the corresponding salary totals for each department displayed in the rows.

You can also use the UNPIVOT operator in Teradata to reverse the operation of PIVOT, rotating columns of data back into rows.

## Summary

I hope this post helped you to understand the PIVOT function fundamentals and how to use it in Teradata.

Could You Please Share This Post?I appreciate It And Thank YOU! :)Have A Nice Day!