How do we perform the following task in crosstab?

Question

Example of ProductSales table.

--Table create table productsales ( country varchar(20), salesman varchar(20), amount integer )

--Insert couple of records into productsales insert into productsales values('UK','Sam',25000); ... ...

--Query to be perform in "crosstab" in plpgsql, instead of using pivot operator. select salesman,UK,US,UAE from productsales pivot -- I know this will not work in plpgsql ( sum(amount) for country IN ([UK],[US],[UAE]) ) AS pt

MatheusOl MatheusOl · Accepted Answer · 2014-02-17T12:02:08

To create a crosstab you can use the tablefunc module that comes with PostgreSQL. You need first to install it (the way of doing that will depend on how you installed PostgreSQL), and then create the extension:

CREATE EXTENSION tablefunc;

With that in place, you can simple do your crosstab query:

SELECT * FROM crosstab($$
        /* Your normal query with your own filters (the fields must be always at the same order) */
        SELECT salesman, country, amount
        FROM productsales
        WHERE country IN ('UAE','UK','US')
        /* The ORDER is really important here */
        ORDER BY 1, 2
    $$,
    /* The values that will be crossed, notice they are ordered alphabetically */
    $$VALUES('UAE'),('UK'),('US')$$
) AS
/* Here you tell which columns and types you expect */
productsales(salesman varchar(20), uae integer, uk integer, us integer);

How do we perform the following task in crosstab?

1 Answers