The Art of Data Reshaping | GBase 8s Database Pivot Function
As an enterprise-grade database product, GBase 8s delivers outstanding data processing capabilities. In day-to-day data processing, whether for reporting or data analysis, we often need to convert rows into columns. The Pivot function perfectly addresses this need, effortlessly converting row data into columnar format and enabling us to view data from different perspectives. This article details how to use the Pivot function for dynamic row-to-column transformations, along with concrete examples.
Overview of GBase 8s Pivot Function
Pivot, or row-to-column, is a process of converting row data in a table into column data. Through Pivot operations, we can transform one or more columns of data into multiple columns for better horizontal statistics and analysis.
Unpivot, or column-to-row, is the opposite: it converts column data into row data, facilitating vertical statistics and analysis.
Pivot and Unpivot are two highly useful data transformation functions. They reshape data presentation as needed, making it easier to understand and analyze. They have wide applications in database queries, data analysis, report generation, etc. We will cover the Unpivot function in an upcoming article.
Detailed Syntax of GBase 8s Pivot
The Pivot transformation process: multiple rows are aggregated and then transformed into columns, where each column represents a different range of the aggregated data. The syntax is as follows:
The PIVOT clause immediately follows FROM and is placed between FROM and WHERE.
In a nutshell: pivot(aggregate_function1 AS alias1, aggregate_function2 AS alias2 ...) FOR column_to_be_transformed1, column_to_be_transformed2 ... IN ((value1_1, value2_1) AS alias1, (value1_2, value2_2) AS alias2 ...)
GBase 8s Pivot Examples
Note: The database version used for the following examples is: GBase8sV8.8_TL_3.5.1_x86_64
The following examples demonstrate simple usage of the Pivot syntax using the emp table. Before transformation, let's look at the base data:
--Example 1: Single-Column Pivot
Here we will aggregate sal1 by deptno and job, then pivot the aggregated deptno summaries into separate columns.
Column to aggregate: sal1
Column to pivot: deptno
Values to include in the pivot: 10,20,30,40
Points to note about this example:
The Pivot operation implicitly performs a GROUP BY using columns not referenced in the pivot_clause (in this case, job and deptno). Most pivot queries are executed on a specific subset of columns. As with any aggregate query, including extra columns affects grouping. In this example, all columns except sal1 become grouping columns, with deptno as the pivot column. If additional columns from the base data, such as ename, were included, they would affect grouping; therefore, you can use subqueries to define the base column set. In the example, the purpose of SELECT deptno, job, sal 1 FROM emp is to define the base column set.
--Example 2: Effect of Including All Columns
In this example, all columns except sal1 become grouping columns, with deptno as the pivot column. This pivot does not provide meaningful results.
--Example 3: Using WITH AS
Below is another example using a WITH AS subquery, which yields the same results as the inline view above.
--Example 4: Multi-Column Pivot
In the example above, the pivot_clause defines two aggregates, resulting in twice as many pivot columns (because there are two aggregates).
Number of pivot columns = number of aggregates * number of values in the pivot_in_clause (2*2=4),
The number of pivot columns cannot exceed 1024.
--Example 5: Multi-Column, Multi-Value Pivot
Notes on GBase 8s Pivot Queries
Columns used in the pivot_for_clause (e.g., deptno in the examples) cannot appear in the SELECT projection list. They are grouped according to the values provided in the pivot_in_clause. In the following attempt to query the deptno column, an error will be raised because deptno is completely removed from the projection.
Similarly, you cannot query any column that appears in the pivot_clause. For instance, trying to query the sal1 column will also raise an error.
Columns in the pivot_clause must use an aggregate function; otherwise, an error is raised.
In the pivot_in_clause, if an alias is specified, it will be used as the column name; otherwise, the value itself becomes the column name. In the pivot_clause, if an alias is specified, it is appended to the name of the corresponding pivoted column; if omitted, nothing is appended. When multiple aggregate functions are used in the pivot_clause, GBase 8s allows leaving aliases undefined, in which case it automatically appends _1, _2, etc. Aliases must not be enclosed in single quotes.
For other usage restrictions, refer to the GBase 8s V8.8 SQL Guide: Syntax.pdf
GBase 8s Official Documentation: https://www.gbase.cn/download/gbase-8s-1?category=DOCUMENT
GBase Technical Community: https://www.gbase.cn/community
Through this article, you should now have a comprehensive understanding of the Pivot feature in GBase 8s. It is more than a simple data transformation tool; it is a powerful instrument for data analysis. In the next article, we will dive into tips for using Unpivot. Thank you for reading.