Data Unpivoting | Unpivot Function in GBase 8s Database

Published on 2024-09-29

In daily data processing, whether for report generation or data analysis, it often involves pivoting rows and columns to aggregate data purposefully, enabling ideal data pivoting. As an enterprise-grade database product, the GBase 8s database provides Pivot and its reverse operation, Unpivot. In our previous article, we analyzed the Pivot function in detail (Click to view Pivot function usage). In this article, let's explore the Unpivot function.

Unpivot Function Overview

Unpivot is the reverse of the Pivot operation, transforming columns into rows. It is especially useful when dealing with multiple related values (often representing different categories or time points) stored across multiple columns. This transformation distributes what were multiple column values into multiple rows, enabling vertical analysis, queries, or integration with other datasets.

Unpivot Syntax Details

In GBase 8s, the syntax for the Unpivot operation is equally straightforward. By specifying target and source fields, you can convert data from column format to row format for in-depth analysis.

Unpivot Usage Example

First, create a result set after a Pivot operation to demonstrate Unpivot.

Use Case:

Considerations for Unpivot Queries

  • Unpivot queries can operate on any column, not just aggregated columns or columns after a Pivot operation. GBase 8s supports unpivoting columns from any table or view.

  • The maximum number of rows returned by an Unpivot query is: number of groups * conversion columns specified in unpivot_in_clause (in the example above, 4 (job) * 3 (conversion columns) = 12, default exclude nulls, excluding null rows, after removing 4 null rows, the result is 8).

  • GBase 8s limits the number of conversion columns specified in unpivot_in_clause to a maximum of 256 columns.

  • GBase 8s requires that columns in unpivot_in_clause must be of the same data type.

  • GBase 8s requires that the number of items in the unpivot_for clause matches the number of AS expressions in the unpivot_in clause.

  • In Pivot, aliases can be specified in pivot_in_clause and pivot_clause. Unpivot syntax also allows aliases, but only columns defined in unpivot_in_clause can be aliased. In GBase 8s, only constant expressions are allowed for aliases, and aliases in unpivot_in_clause cannot be enclosed in single quotes.

Closing Notes

For other Unpivot usage restrictions, refer to the official documentation "GBase 8s V8.8 SQL Guide: Syntax.pdf"

Official documentation link: https://www.gbase.cn/download/gbase-8s-1?category=DOCUMENT

You are also welcome to visit the GBase Technology Community at https://www.gbase.cn/community to share and discuss your GBase database questions and usage experiences.