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

Using conditional aggregation is almost always easier than using `crosstab()` — , Apr 30 '21 at 10:34
Thanks. Could you give an example or point me to any tutorials please? — StuP, Apr 30 '21 at 10:35
See [here](https://stackoverflow.com/questions/58062225/how-to-create-a-postgresql-pivot-table-that-pivots-multiple-columns) or [here](https://stackoverflow.com/questions/56568101/using-postgres-tablefunc-crosstab-to-count-incorrect-answers) or [here](https://stackoverflow.com/questions/67174469/how-do-you-convert-rows-to-columns-in-postgres/67175030#67175030) or [here](https://stackoverflow.com/questions/66743282/dynamic-transpose-for-unknown-row-value-into-column-name-on-postgres/66744030#66744030) — , Apr 30 '21 at 10:38
Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names — , Apr 30 '21 at 10:41

score 1 · Accepted Answer · answered Apr 30 '21 at 11:47

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.

CrossTab function posgresql

1 Answers1