Redshift Pivot and Unpivot Functions: A Comprehensive Guide

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

Pivot and unpivot are common functions in relational databases for converting rows to columns and vice versa. These function are supported by many relational databases, including Amazon Redshift, which has recently added support for PIVOT and UNPIVOT functions. However, other methods such as CASE or DECODE can be used as alternatives for converting rows to columns or columns to rows. In this article, we will explore the Redshift pivot and unpivot table functions in detail for transforming your data.

Post Content

Introduction

Amazon Redshift is a leading cloud-based data warehousing solution by Amazon Web Services (AWS) that offers cost-effective and scalable data storage and analysis. Redshift is considered one of the best cloud data warehouse solutions due to its ease in processing and analyzing vast amounts of data.

Recently, Redshift added support for Pivot and Unpivot tables, which allow you to transform your data sets into more useful and meaningful formats. By using Pivot tables, you can easily summarize and aggregate data, while Unpivot tables enable you to query output data by converting columns into rows. In this post, we’ll explore these two concepts and how they can be used in Redshift to optimize data analysis and decision making, providing you with practical examples and tutorials to help you get started.

Redshift Pivot and Unpivot Functions

Pivot Tables 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 tables is a common requirement in data warehousing and reporting queries. While Microsoft Excel is a popular tool for creating pivot tables, Redshift now supports the PIVOT function, making it easy to transpose rows to column. With AWS Redshift’s recent support for PIVOT, queries can use this function to transform data.

Transpose Rows to Column in Redshift using Pivot Example

To illustrate the of pivot function, let’s consider a scenario where you have a sales_data table that includes columns for product, region, and sales_volume. By using a pivot table, you can easily group the data by region and product, with sales_volume as the aggregated metric. The query output can help you quickly identify which products are selling the most in each region.

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 Tables 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.

Transpose Columns to Rows in Redshift using Unpivot Example

The UNPIVOT function is used to transform the data from a wide format. 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 |

Difference Between Pivot and Unpivot Functions in Redshift

Pivot and Unpivot functions can be used to transform data in different ways within AWS Redshift. Here are some key differences between them:

  • The Redshift PIVOT function rotates a table by converting rows into columns, while the Redshift UNPIVOT function converts columns into rows.
  • Although the syntax for PIVOT and UNPIVOT functions is similar, there are some differences. For example, PIVOT requires you to specify the column that you want to pivot along with the aggregate column, while UNPIVOT requires you to specify the columns that you want to unpivot.
  • The output of PIVOT and UNPIVOT functions is also different. PIVOT generates a new query output with rotated rows as a columns, while UNPIVOT generates a new query output with unpivoted columns as rows.
  • The performance of PIVOT and UNPIVOT functions depends on the size and complexity of the data being transformed. In general, UNPIVOT operations can be more complex and slower than PIVOT operations due to the additional processing required.

Conclusion

Finally to sum up, AWS Redshift provides you with the PIVOT and UNPIVOT functions that offer flexibility and convenience when working with large and complex datasets. The PIVOT function is useful for rotating rows into columns, while the UNPIVOT function can be used to convert columns into rows. By using these functions, users can transform data in different ways and optimize their data analysis processes.

Related Articles,

Tags: , , , , , ,