Re: CROSSTAB( .. only one column has values... ) - Mailing list pgsql-general

On Tue, Jan 5, 2021 at 8:46 AM Adam Tauno Williams <awilliam@whitemice.org> wrote:

Only the first column has values, all the rest are NULL.
I assume I am missing something stupid.

I think you are assigning the function more intelligence/effort than it puts out.

From the documentation:

"""

The crosstab function produces one output row for each consecutive group of input rows with the same row_name value. It fills the output value columns, left to right, with the value fields from these rows. If there are fewer rows in a group than there are output value columns, the extra output columns are filled with nulls; if there are more rows, the extra input rows are skipped.

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page.

"""

The fact you don't have an order by, and that there are not an equal number of records per date, suggests to me that you are expecting the function to fill in the blanks when the documentation says it doesn't do that.

David J.

+1

imho the crosstab() function isn't a good implementation. The biggest failure it has is that you must know exactly how many output columns you will have in the result prior to running it