CrossTab function posgresql

Question

I am new to postgresql and even newer to crosstab but from what I have read, the below query should work:

select * from crosstab(
$$select distinct "AccountNumber" , "ProductCategory", sum("ProductQuantity") sm
from "MsfDataRecords" mdr
group by "ProductCategory", "AccountNumber", "ProductQuantity"$$
)as ct("AccountNumber" text , "ProductCategory" text , sm numeric)

But this errors with SQL Error [42601]: ERROR: return and sql tuple descriptions are incompatible

I have checked all the data types and they are correct. I'm not sure if it is to do with the sum function though.

Any help appreciated

Answer 1

The error is in the last line. The columns represented in the ct are selected in this line. Instead of

)as ct("AccountNumber" text , "ProductCategory" text , sm numeric)

it should be

as ct(
    AccountNumber text, 
    ProductCategory1 numeric, 
    ProductCategory2 numeric, 
    ProductCategory3 numeric, 
    ..., 
    ProductCategoryN numeric)

Your GROUP BY clause also should only include the first and second column which are later on sorted.

Here's a dbfiddle example for illustration. Or if you prefer code here's an example code.

CREATE TABLE MsfDataRecords(
    AccountNumber text,
    ProductCategory text,
    ProductQuantity numeric
)
;

INSERT INTO MsfDataRecords(AccountNumber, ProductCategory, ProductQuantity) VALUES
    ('A1', 'Food', 3),
    ('A1', 'Food', 1),
    ('A2', 'Food', 3),
    ('A2', 'Electronics', 2),
    ('A2', 'Fashion', 10)
;

SELECT * FROM CROSSTAB(
    $$
    SELECT AccountNumber , ProductCategory, SUM(ProductQuantity) AS sm
    FROM MsfDataRecords AS mdr
    GROUP BY 1,2
    ORDER BY 1,2
    $$
)AS ct(
    AccountNumber text,
    Food numeric,
    Electronics numeric,
    Fashion numeric
)
;

Note that pivoting like this only works in PostgreSQL.