Redshift Pivot and Unpivot Table-Transpose Redshift Table

  • Post author:
  • Post last modified:April 26, 2023
  • Post category:Redshift
  • Reading time:9 mins read

In the relational database, Pivot used to convert rows to columns and vice versa. Many relational databases support pivot function. Recently, Amazon Redshift started supporting PIVOT and UNPIVOT function. However, as an alternative method, you can use CASE or DECODE to convert rows to columns, or columns to rows. In this article, we will check Redshift pivot and unpivot table methods to convert rows to columns and vice versa.

Post Content

Introduction

Amazon Redshift is a cloud-based data warehousing solution provided by Amazon Web Services (AWS) that enables businesses to store and analyze large amounts of data in a cost-effective and scalable way. Redshift is one of the best cloud data warehouse solutions for its ability to process and analyze vast amounts of data with ease.

Recently, Redshift started providing support for Pivot and Unpivot tables, which can be used to transform data sets into more useful and meaningful formats. Pivot tables allow you to aggregate and summarize data, while Unpivot tables enable you to restructure data by converting columns into rows. In this post, we will delve deeper into these two concepts and explore how they can be leveraged within Redshift to optimize data analysis and decision making.

Pivot Table in Redshift

PIVOT is a parameters in the FROM clause that rotate query output from rows to columns. It represent tabular query results in a format that’s easy to read.

Creating pivot table is a relatively common need in a data warehouse or reporting queries. Microsoft excel is a popular tool that allows you to pivot tables based on your requirement. Implementing same thing in Redshift is a fairly easy task now as AWS Redshift started supporting PIVOT now.

Redshift Transpose Rows to Column using Pivot Example

Let’s say, for example, that you have a table of sales_data that includes columns for product, region, and sales_volume. You could use a pivot table to group the data by region and product, with sales_volume as the aggregated metric. This would allow you to quickly see which products are selling the most in each region, and identify any trends or patterns in the data.

Here is the sample data:

|region|product  |sales_volume|
|------|---------|------------|
|North |Product A|1,000       |
|North |Product B|2,000       |
|North |Product C|1,500       |
|South |Product A|500         |
|South |Product B|2,500       |
|South |Product C|1,000       |
|East  |Product A|2,000       |
|East  |Product B|1,000       |
|East  |Product C|500         |
|West  |Product A|1,500       |
|West  |Product B|3,000       |
|West  |Product C|2,500       |

Now, assume you want to transpose rows to column to see which products are selling the most in each region. i.e. Transpose “Product A”, “Product B” and “Product C” values as a column.

You can use following Redshift query to pivot the Redshift table:

SELECT *
FROM (
	SELECT
		region,
		product,
		sales_volume
	FROM
		sales_data
) PIVOT (
  SUM(sales_volume) 
  FOR product IN ('Product A', 'Product B', 'Product C')
);

|region|product a|product b|product c|
|------|---------|---------|---------|
|North |1,000    |2,000    |1,500    |
|South |500      |2,500    |1,000    |
|East  |2,000    |1,000    |500      |
|West  |1,500    |3,000    |2,500    |

Unpivot Table in Redshift

UNPIVOT is a parameters in the FROM clause that rotate query output from columns to rows. It represent tabular query results in a format that’s easy to read.

Redshift Transpose Columns to Rows using Unpivot Example

Here is the sample data:

|quality|red|green|blue|
|-------|---|-----|----|
|high   |15 |20   |7   |
|normal |35 |     |40  |
|low    |10 |23   |    |

Now, consider following UNPIVOT on input columns red, green, and blue.

SELECT	*
FROM (
	SELECT
		red,
		green,
		blue
	FROM
		count_by_color) 
	UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

|color|cnt|
|-----|---|
|red  |15 |
|red  |35 |
|red  |10 |
|green|20 |
|green|23 |
|blue |7  |
|blue |40 |

Comparison of Pivot and Unpivot Tables in Redshift

Pivot and Unpivot tables are functions that can be used to transform data in different ways. Here are some key differences between the two functions in Redshift:

  • The PIVOT function is used to rotate a table, converting columns into rows. The UNPIVOT function, on the other hand, converts rows into columns.
  • The syntax for PIVOT and UNPIVOT functions is similar. The PIVOT function requires you to specify the column that you want to pivot along with aggregate column, while the UNPIVOT function requires you to specify the columns that you want to unpivot.
  • The output of PIVOT and UNPIVOT functions is also different. The PIVOT function generates a new table with the rotated columns as rows, while the UNPIVOT function generates a new table with the unpivoted rows as columns.
  • The performance of PIVOT and UNPIVOT functions depends on the size and complexity of the data being transformed. In general, UNPIVOT operations are more complex and can be slower than PIVOT operations.

Conclusion

In conclusion, the PIVOT and UNPIVOT functions in Redshift allow users to transform data in different ways. PIVOT function can be used to rotate columns into rows, while UNPIVOT function can be used to convert rows into columns. These functions offer flexibility and convenience when dealing with large and complex datasets.

Related Articles

Hope this helps 🙂

Tags: , ,