Running into issues using CROSSTAB in PostgreSQL to transform data from long to wide. Here are the tables that I get before and after running CROSSTAB.
Table returned from source query
| agency | complaint_type | num_complaints |
|---|---|---|
| DOHMH | Rodent | 31623 |
| DOHMH | Food Establishment | 10523 |
| DSNY | Smoking | 2473 |
| DSNY | Air Quality | 10432 |
| ... | ... | ... |
Table returned after pivoting on column 'agency'
| complaint_type | DOHMH | DSNY |
|---|---|---|
| DOHMH | 31623 | 10523 |
| DSNY | 2473 | 10432 |
The output is incorrect as it does not retain the values in the 'complaint_type' column, but rather replaces the values with the new column headers "DOMHM" and "DSNY".
The code I used:
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM CROSSTAB($$
SELECT agency, complaint_type, COUNT(*) AS num_complaints
FROM (
SELECT agency, complaint_type
FROM general
WHERE agency = 'DOHMH' OR agency = 'DSNY') AS subquery
GROUP BY agency, complaint_type
ORDER BY agency, num_complaints DESC;
$$) AS ct (
complaint_type text,
"DOHMH" bigint,
"DDSNY" bigint);
Thank you in advance!!!
