postgres pivot

In Postgres, pivoting refers to the process of transforming rows into columns. This can be useful when analyzing data that is stored in a long format but needs to be presented in a wide format for easier analysis.

There are several ways to pivot data in Postgres, including using the crosstab function, the pivot clause in the SELECT statement, and the pivot_table function in the tablefunc extension.

The crosstab function is a built-in function in Postgres that allows you to pivot data based on a specified set of row and column values. It requires the tablefunc extension to be enabled and uses the following syntax:

SELECT * FROM crosstab('SELECT row_id, col_id, value FROM my_table ORDER BY 1,2') AS ct(row_name text, col_name text, value text);

The pivot clause in the SELECT statement is another way to pivot data in Postgres. It uses the following syntax:

SELECT * FROM (SELECT row_id, col_id, value FROM my_table) AS t PIVOT(max(value) FOR col_id IN ('col1', 'col2', 'col3')) AS p;

The pivot_table function in the tablefunc extension can also be used to pivot data in Postgres. It uses the following syntax:

SELECT * FROM pivot_table('SELECT row_id, col_id, value FROM my_table', 'col_id', ARRAY['col1', 'col2', 'col3']) AS pt(row_name text, "col1" text, "col2" text, "col3" text);

Overall, pivoting in Postgres can be a powerful tool for data analysis and presentation, but it requires careful consideration of the data structure and the appropriate pivot method to use.