Re: CROSSTAB( .. only one column has values... )

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