Re: crosstab function

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(at)compulab(dot)co(dot)il <mailto:sim(at)compulab(dot)co(dot)il>>
>
> 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(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>