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
