From: Caleb Simonyi-Gindele <csg@bordervet.ca>
To: pgsql-sql@postgresql.org
Subject: contrib/tablefunc crosstab
Date: Thu, 05 Aug 2004 11:21:05 -0600
Message-ID: <000001c47b10$97178250$c201a8c0@borderveygqj37> (raw)
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.07
2003-06-03 | 518.62 | 2115.57 | 630.83 | 195.74 | 548.26
2003-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: csg@bordervet.ca
Subject: Re: contrib/tablefunc crosstab
In-Reply-To: <000001c47b10$97178250$c201a8c0@borderveygqj37>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox