Introduction to GBase 8s Database Unpivot Function

Overview of the GBase 8s Unpivot Function

In database operations, data pivoting is a common requirement, involving transforming row data into column data (Pivot) or converting column data into row data (Unpivot). In a previous article, we introduced the Pivot function. In this article, we will dive into the Unpivot function.

To read the previous article, click here: Introduction to GBase 8s Pivot Function

Unpivot is the reverse process of the Pivot operation. It transforms one or more columns in a table into multiple rows, where each row contains a unique value. This transformation allows values originally in columns to be distributed across multiple rows, enabling vertical analysis of the data.

Detailed Syntax of GBase 8s Unpivot

In GBase 8s, the Unpivot operation is simple and intuitive. By specifying target and source fields, you can convert data from a column format to a row format, allowing for deeper data analysis.

SELECT ...
    FROM ...
    UNPIVOT [INCLUDE|EXCLUDE NULLS]   -- Whether to include NULL rows in the result, defaults to excluding NULLs
    (
        unpivot_val_col_lst            -- Target field, the name of the column containing the new values
        unpivot_for_clause             -- Target field, the name of the column after the row transformation
        unpivot_in_clause              -- Source fields, the names of the columns to be converted into rows
    )
WHERE ...

GBase 8s Unpivot Example

Let's first create a result set after a pivot operation to demonstrate the Unpivot function.

create view v_emp as select * from (SELECT deptno,job, sal1 FROM emp) 
PIVOT( SUM(sal1) 
FOR deptno IN
 (10 as d1, 20 d2,30 d3)) ;
select * from v_emp;
Result:
job             d1               d2               d3
president        5000.00
manager        2450.00        5825.00
salesman        1500.00        1250.00          2850.00
clerk            800.00         3000.00

4 row(s) retrieved.

Use Case 1:

SELECT * FROM v_emp
UNPIVOT
(
    deptsal              -- The column name that holds the values after transformation
    FOR saldesc          -- The column name that will hold the labels for the transformed rows
    IN (d1, d2, d3)      -- The columns to be converted into rows
);
Result:

job         saldesc          deptsal
president    d1               5000.00                   
manager     d1               2450.00
manager     d2               5825.00
salesman     d1               1500.00
salesman     d2               1250.00
salesman     d3               2850.00
clerk          d1                800.00
clerk         d2               3000.00
8 row(s) retrieved.

Notes on GBase 8s Unpivot Query

The unpivot query can be applied to any table, not just aggregated columns or columns after a pivot operation. It can be used on any table or view.

The maximum number of rows that an unpivot query can return is: number of groups * the number of columns specified in the unpivot_in_clause (in the example above: 4 (job) * 3 (columns to be transformed) = 12. By default, NULLs are excluded, so 4 NULL rows are omitted, resulting in 8 rows).

• GBase 8s limits the number of columns specified in the unpivot_in_clause to a maximum of 256.
• GBase 8s requires that the columns in the unpivot_in_clause must be of the same data type.
• GBase 8s requires that the number of items in the unpivot_for clause must match the number of items in the unpivot_in_clause.

In the pivot operation, you can specify aliases for the pivot_in_clause and pivot_clause. Similarly, in unpivot, you can use aliases, but only for the columns defined in the unpivot_in_clause. In GBase 8s, these aliases must be constant expressions, and GBase 8s does not support enclosing aliases in single quotes.

Conclusion

That’s all for today’s introduction to the GBase database unpivot function. Thanks for reading!