On 1/5/21 10:46 AM, Adam Tauno Williams wrote: > I'm using the crosstab feature and do not understand why I am only > getting values in the first column. <snip> > So I put this in as a crosstab: > > SELECT * FROM crosstab( > $$ <snip> > $$) AS final_result (invoice_date VARCHAR, "parts" BIGINT, "rental" > BIGINT, "sales" BIGINT, "service" BIGINT); > > - and I get the results of - > > invoice_date parts rental sales service > ------------ ----- ------ ------ ------- > 2001-09 1 (null) (null) (null) > 2007-07 1 (null) (null) (null) > 2013-02 5353 (null) (null) (null) > 2013-02 3454 (null) (null) (null) > 2013-03 3512 (null) (null) (null) > 2013-03 5366 (null) (null) (null) > ... > > Only the first column has values, all the rest are NULL. > I assume I am missing something stupid. Seems you are using the wrong form of the crosstab() function. See https://www.postgresql.org/docs/current/tablefunc.html#id-1.11.7.47.5.7.2 "The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn't work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns." HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Attachment:
signature.asc
Description: OpenPGP digital signature
