GBase 8a Pivot: Turning Rows into Columns

#gbase #database #数据库 #sql

GBase 8a provides the pivot function for data pivoting — converting multiple rows into columns. This article demonstrates the syntax and common patterns using student score data in a gbase database.

Syntax

PIVOT(
  <aggregate function>(<column>)[ AS alias] [, aggregate(...)[ AS alias]...]
  FOR <col1 [, col2 ...]>
    IN (constant1 [AS alias1] [, constant2 [AS alias2] ...])
)

Aggregate part – defines the values for the new columns. Multiple aggregates are allowed. Provide aliases to avoid duplicate column names.
FOR clause – the column(s) to pivot. Single columns can omit parentheses; multiple columns must be wrapped in parentheses and the combined values treated as a single constant.
IN clause – each constant generates a new column. Wrap string/date constants in single quotes. Use aliases for readable column names, especially with multi‑column pivots.

Columns not referenced in FOR or in an aggregate function are implicitly treated as GROUP BY columns.

Sample Table and Data

CREATE TABLE tp (name VARCHAR(10), class VARCHAR(10), score INT);
INSERT INTO tp VALUES
('Zhang San','Math',99), ('Zhang San','Math',129),
('Li Si','Math',88), ('Li Si','Math',128),
('Wang Wu','Math',111), ('Wang Wu','Math',161),
('Zhang San','Chinese',120), ('Zhang San','Chinese',120),
('Li Si','Chinese',140), ('Li Si','Chinese',150),
('Wang Wu','Chinese',100), ('Wang Wu','Chinese',120);

Single Aggregate

Total score per subject:

SELECT * FROM tp PIVOT(SUM(score) FOR class IN ('Chinese','Math'));
+--------+----------+------+
| name   | Chinese  | Math |
+--------+----------+------+
| Li Si  |      290 |  216 |
| Zhang San |   240 |  228 |
| Wang Wu|      220 |  272 |
+--------+----------+------+

Multiple Aggregates

Sum and average with aliases:

SELECT * FROM tp PIVOT(SUM(score) sum, AVG(score) avg FOR class IN ('Chinese','Math'));
+--------+-------------+----------+-------------+----------+
| name   | Chinese_sum | Math_sum | Chinese_avg | Math_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|
+--------+-------------+----------+-------------+----------+

Aliasing the Constant Values

Rename the subject columns for brevity:

SELECT * FROM tp PIVOT(SUM(score) sum, AVG(score) avg FOR class IN ('Chinese' yw, 'Math' sx));
+--------+--------+--------+---------+---------+
| name   | yw_sum | sx_sum | yw_avg  | sx_avg  |
+--------+--------+--------+---------+---------+
| Li Si  |    290 |    216 | 145.000 | 108.000 |
| Zhang San|  240 |    228 | 120.000 | 114.000 |
| Wang Wu|    220 |    272 | 110.000 | 136.000 |
+--------+--------+--------+---------+---------+

Pivoting on Multiple Columns

After adding an age column, you can pivot on a combined (name, age) key. Always use aliases for readability.

SELECT * FROM tp2 PIVOT(SUM(score) sum FOR (name, age) IN (('Zhang San',40) zs40, ('Li Si',45) ls45));
+--------+----------+----------+
| class  | zs40_sum | ls45_sum |
+--------+----------+----------+
| Chinese|      240 |      290 |
| Math   |      228 |      216 |
+--------+----------+----------+

Without aliases, the column names become unreadable strings.

pivot in GBase 8a gives you native SQL support for reshaping data, making cross‑tab reports and summary tables much easier to write. Combine it with unpivot to move seamlessly between wide and long formats in your gbase database.

DEV Community