contrib/tablefunc crosstab - Mailing list pgsql-sql

From Caleb Simonyi-Gindele
Subject contrib/tablefunc crosstab
Date
Msg-id 000001c47b10$97178250$c201a8c0@borderveygqj37
Whole thread Raw
Responses Re: contrib/tablefunc crosstab
List pgsql-sql
Hi,

I am using the crosstab function by Joe Conway and I am having a problem
that could be my fault but I'm not sure.

My query/result is:

atc=# SELECT * FROM crosstab('SELECT cli_tran_trans_date,
TRIM(dat_staff_code) AS dat_staff_code, SUM(cli_tran_amount) from vetpmardet
WHERE cli_tran_trans_date BETWEEN ''2003-06-01'' AND ''2003-06-05'' AND
TRIM(dat_staff_code) NOT LIKE '''' GROUP BY cli_tran_trans_date,
dat_staff_code ORDER BY 1,2;',5) AS vetpmardet(date date, vet1 numeric, vet2
numeric, vet3 numeric, vet4 numeric, vet5 numeric);
   date    |  vet1  |  vet2   |  vet3   |  vet4  |  vet5
------------+--------+---------+---------+--------+--------2003-06-01 | 561.99 |         |         |        |2003-06-02
|418.43 |  548.10 |  790.16 |  54.24 | 467.072003-06-03 | 518.62 | 2115.57 |  630.83 | 195.74 | 548.262003-06-04 |
870.52|  346.49 |  393.77 | 470.02 |2003-06-05 |  74.37 | 3555.10 | 1248.69 | 758.18 |
 
(5 rows)

My problem is that the rows are populated from right to left irrespective of
the column. So the $561.99 in vet1 actually belongs to vet2. There are never
any blank cells in the middle or start of a row - they always come at the
end. How do I get the amount for that date into the appropriate column?

I am using 7.4.3

Caleb 




pgsql-sql by date:

Previous
From: Michael Glaesemann
Date:
Subject: Re: Datetime
Next
From: Joe Conway
Date:
Subject: Re: contrib/tablefunc crosstab