PostgreSQL - CrossTab Queries using tablefunc contrib

PostgreSQL statement uses the function crosstab to pivot the table records, pivot means I want to convert the rows to the column of particular column's value and want to the others column value respectively of converted rows.

PostgreSQL- CROSSTAB example

Suppose we have the following table

CREATE TABLE product (   id serial not null,   brand text,   category text,   qty integer ); -- Insert some records INSERT INTO product(brand,category,qty)   VALUES('Arrow','Cloths',3000); INSERT INTO product(brand,category,qty)   VALUES('Samsung','Mobile',4500); INSERT INTO product(brand,category,qty)   VALUES('iPad','Tablet',2000); INSERT INTO product(brand,category,qty)   VALUES('Prestige','Kitchen',200);

Now, select query

SELECT * FROM product;

Result:

id	brand	category	qty
1	"Arrow"	"Cloths"	3000
2	"Samsung"	"Mobile"	4500
3	"iPad"	"Tablet"	2000
4	"Prestige"	"Kitchen"	200

But expected result as below

Result:

brand	Cloths	mobile	Tablet	kitchen
"Arrow"	3000
"iPad"			2000
"Prestige"				200
"Samsung"		4500

Using CROSSTAB we can achieve the pivot goal, se the following query

SELECT * FROM   crosstab(    'SELECT brand, category, qty     FROM   product     ORDER  BY 1,2',                 $$VALUES ('Cloths'::text), ('Mobile'),('Tablet'),('Kitchen')$$    ) AS ct (brand text, Cloths int, Mobile int, Tablet int, Kitchen int);

 Result:

brand	Cloths	Mobile	Tablet	kitchen
"Arrow"	3000
"iPad"			2000
"Prestige"				200
"Samsung"		4500

Note: If you are getting the following error:

ERROR:  function crosstab(unknown, unknown) does not exist

LINE 2: FROM   crosstab(

               ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SQL state: 42883

Character: 17

The above error will remove executing the following PostgreSQL statement

CREATE EXTENSION IF NOT EXISTS tablefunc;