Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Reference/ Functions and Operators/ Pivot Table Functions
Updated on 2024-08-20 GMT+08:00
View PDF
Share

Pivot Table Functions

  • tablefunc()

    Description: Extended API for processing table data, including pivot table functions. Only the system administrator can install extensions.

    By default, the extension is installed in the public schema. You are advised to install the extension in the user schema by running create extension tablefunc [schema {user_schema}]. The extended function is for internal use only. You are advised not to use it.

  • crosstab(source_sql text [, N int])

    Description: Uses the result of source_sql as the source data to generate a pivot table.

    Return type: setof record

    Example:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    gaussdb=# CREATE extension tablefunc;
CREATE EXTENSION
gaussdb=# CREATE TABLE cross_test(group_id text, id int, var text);
CREATE TABLE
gaussdb=# SELECT * FROM cross_test;
 group_id | id | var 
----------+----+-----
(0 rows)

gaussdb=# SELECT * FROM crosstab('SELECT group_id, var FROM cross_test order by 1, 2;') AS c(group_ text, cat1 text, cat2 text, cat3 text);
 group_ | cat1 | cat2 | cat3 
--------+------+------+------
(0 rows)

    N is a deprecated parameter and does not affect the function result.

  • crosstabN(source_sql text)

    Description: Uses the result of source_sql as the source data to generate a pivot table with "N+1" columns. crosstabN is a group of functions, including crosstab2, crosstab3, and crosstab4.

    Return type: setof tablefunc_crosstab_N. tablefunc_crosstab_N includes tablefunc_crosstab_2, tablefunc_crosstab_3, and tablefunc_crosstab_4.

    Example:

    1
2
3
4
5
6
7
8
9
    		 
    -- If N in crossbatN(source_sql text) is set to 2, a pivot table with three columns is generated.
gaussdb=# CREATE extension tablefunc;
CREATE EXTENSION
gaussdb=# CREATE TABLE cross_test(group_id text, id int, var text);
CREATE TABLE
gaussdb=# SELECT * FROM crosstab2('SELECT group_id, var FROM cross_test ORDER BY 1, 2;');
 row_name | category_1 | category_2 
----------+------------+------------
(0 rows)
  • crosstab(source_sql text, category_sql text)

    Description: Uses the result of source_sql as the source data to generate a pivot table based on the result category of category_sql.

    Return type: setof record

    Example:

    1
2
3
4
5
6
7
8
    		 
    gaussdb=# CREATE extension tablefunc;
CREATE EXTENSION
gaussdb=# CREATE TABLE cross_test(group_id text, id int, var text);
CREATE TABLE
gaussdb=# SELECT * FROM crosstab('SELECT group_id, var FROM cross_test order by 1, 2;', 'SELECT generate_series(1, 4)') AS c(group_ text, cat1 text, cat2 text, cat3 text, cat4 text);
 group_ | cat1 | cat2 | cat3 | cat4 
--------+------+------+------+------
(0 rows)
Parent topic: Functions and Operators