Search Postgresql Archives

Re: Problem with Crosstab - Allocating value to wrong column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 10/19/2010 03:07 AM, Stefan Schwarzer wrote:
> For one of the countries, I have a value for 2007, but not for 1960. 
> When using only the inner query, than I see one line: Andorra - 2007
> - 539 But when running the whole SQL, the value for year 2007 get's
> allocated to the year 1960. The table looks as follows:
> 
> name     |    y_1960    |   y_2007
> Andorra  |       539    |   NULL


That is documented behavior. See:
  http://www.postgresql.org/docs/8.4/interactive/tablefunc.html

> F.33.1.2. crosstab(text)
...
> 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.

You probably want the other form of crosstab

> F.33.1.4. crosstab(text, text)

> 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


-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

Attachment: signature.asc
Description: OpenPGP digital signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux