PostgreSQL: Example of CROSSTAB query, for PIVOT arrangement

In this post, I am sharing an example of CROSSTAB query of PostgreSQL. The “tablefunc” module provides the CROSSTAB() which uses for displaying data from rows to columns.

I already shared few similar articles on PostgreSQL PIVOT and new CROSSTABVIEW.

Please check the below demonstration:

Create a required extension:

1	CREATE EXTENSION tablefunc;

Create sample table with data:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE TEMP TABLE test_crosstab (     Product_Name TEXT     ,Product_Category TEXT     ,Product_Count INT );   INSERT INTO test_crosstab VALUES ('Mobile','IT',20) ,('Mobile','ELE',40) ,('Desktop','IT',50) ,('Desktop','ELE',60) ,('Laptop','IT',30) ,('Laptop','ELE',70);

Use CROSSTAB for displaying data from rows to columns:

1 2 3 4 5 6 7

SELECT * FROM CROSSTAB (      'SELECT Product_Name, Product_Category, Product_Count      FROM test_crosstab      ORDER BY 1,2' )AS T (Product_Name text, IT INT, ELE INT)

Result: