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('SLECT 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)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot