The
crosstabfunction 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
crosstabitself 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.
"group by customername,productname order by customername, productname"
instead of
"group by customername,productname order by productname"
HTH
Filip
I rechecked and with products as columns it has duplicate customers. My goal is one row per customer with the sum of quantity filled in for each product they purchased.
create table customers(customerid serial primary key, customername text);
create table products(productid serial primary key, productname text);
create table quotations(quotationid serial primary key, customerid int, orderdate timestamp);
create table quotationitems(quotationitemid serial primary key, quotationid int, productid int, quantity int, unitprice numeric(9,2));
select * from crosstab('
select customername,productname as bucket,sum(quantity) as bucketvalue
from quotationitems a join quotations b using(quotationid)
join customers c using(customerid)
join sales.products d using (productid)
where orderdate between ''1/1/2009'' and ''1/1/2010''
and producttypeid=1
group by customername,productname order by productname',
'select productname from sales.products where producttypeid=1 order by productname')
as rpt(customername text,"ATX" int,
"CM-A510" int,
"CM-F82" int,
"CM-i586" int,
"CM-i686B" int,
"CM-i686M" int,
"CM-iAM" int,
"CM-iGLX" int,
"CM-iPM" int,
"CM-iTC" int,
"CM-T3530" int,
"CM-X255" int,
"CM-X270" int,
"CM-X300" int,
"CM-XAM" int
)
order by customername
On 12/14/2010 10:27 AM, Filip RembiaÅkowski wrote:please show
- source data structures (in form of CREATE TABLE please)
- actual whole query that creates duplicates
2010/12/14 Sim Zacks <sim@xxxxxxxxxxxxxx>
postgres 8.2.17
I am trying out the crosstab function (tablefunc contrib) for reporting needs and I'm having a problem.
I have customers and products and the data is the quantity purchased. I am grouping by customername, productname in the source sql. My category sql depends if I want the products or customers to be the columns.
When I make customers the rows and products the columns, it works fine. But when I make customers the columns and products the rows, there are duplicate product rows.
Is there a way to group the product rows so that the data results come back correct?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
