DEV Community

Michael
Michael

Posted on • Originally published at gbase8.cn

GBase 8a unpivot Function: Turning Columns into Rows

#database #数据库 #gbase

unpivot is the counterpart of the pivot function in GBase 8a. It transforms multiple columns into rows — a classic "wide‑to‑long" transformation that is essential for normalising denormalised data. This article demonstrates the syntax and provides progressive examples using a student score table in a gbase database.

Syntax 

unpivot 
  [{include_nulls | exclude_nulls}]
  (
    COLUMN | (
      COLUMN[,COLUMN]..
    )
    FOR COLUMN | (
      COLUMN[,COLUMN]..
    )
    IN COLUMN | (
      COLUMN[,COLUMN]...
    ) [ AS LITERAL | (
        LITERAL[,LITERAL]...
      )
    ]
  )
Enter fullscreen mode Exit fullscreen mode

Parameter Details

  • NULLS — INCLUDE_NULLS keeps rows where the source column value is NULL; EXCLUDE_NULLS (the default) filters those rows out entirely.
  • Output columns — The function generates two new columns: one holds the original column name (or its alias), and the other holds the corresponding value.

Sample Data

The table tpp stores each student's Chinese and math scores:

gbase> select * from tpp;
+--------+--------+--------+----------+----------+
| name   | yw_sum | sx_sum | yw_avg   | sx_avg   |
+--------+--------+--------+----------+----------+
| Li Si  |    290 |    216 | 145.0000 | 108.0000 |
| Zhang San |  240 |    228 | 120.0000 | 114.0000 |
| Wang Wu |    220 |    272 | 110.0000 | 136.0000 |
+--------+--------+--------+----------+----------+
Enter fullscreen mode Exit fullscreen mode
  • yw_sum / sx_sum — total scores for Chinese and math
  • yw_avg / sx_avg — average scores

Single‑Column Unpivot

Turn the yw_sum column into rows. The new column class stores the category label, and score stores the value.

select * from tpp unpivot(score for class in (yw_sum as 'Chinese Total'));
Enter fullscreen mode Exit fullscreen mode

Multi‑Column Unpivot

Unpivot two columns — Chinese and math total scores — at once:

select * from tpp unpivot(score for class in (yw_sum as 'Chinese Total', sx_sum as 'Math Total'));
Enter fullscreen mode Exit fullscreen mode

This produces 6 rows — two rows per student.

Mixed Data Types

Unpivot three columns, mixing integer totals and decimal averages:

select * from tpp unpivot(score for class in (yw_sum as 'Chinese Total', sx_sum as 'Math Total', yw_avg as 'Chinese Average'));
Enter fullscreen mode Exit fullscreen mode

Full Unpivot of All Columns

Unpivot all four columns to obtain a clean key‑value structure:

select * from tpp unpivot(score for class in (yw_sum as 'Chinese Total', sx_sum as 'Math Total', yw_avg as 'Chinese Average', sx_avg as 'Math Average'));
Enter fullscreen mode Exit fullscreen mode

Result: 12 rows, each containing the student name, the subject label, and the corresponding score.

When to Use unpivot

unpivot is especially useful when:

  • You receive wide‑format reports that need to be normalised for further aggregation or joining.
  • You need to build dynamic dashboards where each metric category should be its own row.
  • You are reshaping data for OLAP analysis or feeding into visualisation tools.

In a gbase database, unpivot provides a clean, SQL‑native way to flatten wide tables into a tidy long format — no complex joins or unions required. Combined with pivot, it gives you full control over data reshaping directly in your GBASE analytical queries.

Top comments (0)