Re: [GENERAL] Crosstab function
Thanks Sim, a very usefull information. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sim Zacks Sent: Wednesday, May 07, 2014 7:33 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Crosstab function What I have done in the past to build a generic reporting application is to have the function write the results you want in a table and return the tablename and then have the client code call select * from that table. My standard report tablename is tblreport || userid; It gets dropped at the beginning of the function, so it is basically a temp table that doesn't interfere with any other users. Example: execute 'drop table if exists reports.tblreport' || v_userid ; execute 'drop sequence if exists reports.tblreport' || v_userid || '_id_seq; create sequence reports.tblreport' || v_userid || '_id_seq'; v_sql=' create table reports.tblreport' || v_userid || ' as '; Sim
Re: [GENERAL] Crosstab function
What I have done in the past to build a generic reporting application is to have the function write the results you want in a table and return the tablename and then have the client code call select * from that table. My standard report tablename is tblreport || userid; It gets dropped at the beginning of the function, so it is basically a temp table that doesn't interfere with any other users. Example: execute 'drop table if exists reports.tblreport' || v_userid ; execute 'drop sequence if exists reports.tblreport' || v_userid || '_id_seq; create sequence reports.tblreport' || v_userid || '_id_seq'; v_sql=' create table reports.tblreport' || v_userid || ' as '; Sim On 05/06/2014 06:37 AM, Hengky Liwandouw wrote: Very Clear instruction ! Thank you very much David. I will do it in my client app and follow your guidance. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G Johnston Sent: Tuesday, May 06, 2014 11:01 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Crosstab function Hengky Lie wrote Hi David, Are you sure that there is no pure sql solution for this ? I think (with my very limited postgres knowledge), function can solve this. Which is the column header I need but I really have no idea how to use this as column header. Anyway, If i can't do this in postgres, I will try to build sql string in the client application (Windev) and send the fixed sql to the server Positive. You could build the necessary string in a pl/pgsql language function but you would still have trouble getting the data out of the function the way you want; unless you output a single text column no matter the original data ( basically output a cvs version of the crosstab result). There is no dynamic execution in SQL so even though you can get a string that looks like what you want you cannot do anything with it. Only data is allowed to be dynamic; the engine has to know the names and types of all schema objects before it can start so there is no way a query can retrieve these things from its own data. It's the whole cart-horse thing... The solution is to build the query in the client and send it. Make sure you look at the various "quote_" functions in order to minimize the risk of SQL injection attacks. These are especially useful for pl/pgsql functions but you might be able to use them in your first query so that you can avoid coding all the quoting and escaping rules into your application. At minimum double-quote all your identifiers and make sure there are no unescaped embedded double-quotes. If the only variables are from data in tables putting constraints on those tables would probably be useful as well - you limit valid identifiers but minimized risk of bad data causing an issue. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Crosstab-function-tp5802402p5802601. html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] Crosstab function
Hi Friends, Could somebody help me with crosstab function ? I have warehouse table: CREATE TABLE tblwarehouse ( id integer NOT NULL, warehousename character varying(20) ); COPY tblwarehouse (id, warehousename) FROM stdin; 2 OFFICE 3 STORE2 \. And product table : CREATE TABLE tblproduct ( id serial NOT NULL, produkid text, warehouseid integer, onhand integer ); COPY tblproduct (produkid, warehouseid, onhand) FROM stdin; 279140414 2 10 279140421 3 10 279140414 3 45 \. I need crosstab function to display record from tblproduct like this : PRODUKID| OFFICE | STORE2 | TOTAL ---+++ -- 279140414 | 10 | 45 | 55 279140421 | 0 | 10 | 10 The crosstab warehouse column name (OFFICE Store2) is taken from tblwarehouse so when user add warehouse, crosstab column name will change automatically. And also each row has total qty. Thanks in advance !
Re: [GENERAL] Crosstab function
Hengky Lie wrote The crosstab warehouse column name (OFFICE Store2) is taken from tblwarehouse so when user add warehouse, crosstab column name will change automatically. And also each row has total qty. In what programming language? The only way to do this is to dynamically construct the appropriate query, with the correct number of columns, on-the-fly in the client application and send it as a normal query to the server. There is no pure SQL solution. For the total column you will need a virtual warehouse that holds those values. Likely the easiest way to get that will be to UNION ALL the main real warehouse query and another query the groups by product and sum-counts that values from the individual warehouses. IIRC you've already been shown how to write the basic crosstab query; this really isn't any different but you will need procedural logic and some way to dynamically build a SQL query string based upon how many warehouses you have at the time you run the query. I am assuming you know how to write the basic join query to get the general form needed for the real warehouse data. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Crosstab-function-tp5802402p5802456.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Crosstab function
Hi David, Are you sure that there is no pure sql solution for this ? I think (with my very limited postgres knowledge), function can solve this. So far i can use command: select * from crosstab ( 'select produkid, warehouseid, onhand from tblproduct order by 1', 'select distinct warehouseid from tblproduct order by 1' ) as ct (produkid text, office int, store2 int); and I have this result : PRODUKID| OFFICE | STORE2 | ---+++ 279140414 | 10 | 45 | 279140421 || 10 | The problem is the column header is static. If I have new warehouse, I should manually add it in the column header. IF I use command : select 'Produk ID text, ' || array_to_string(array(select warehousename from tblwarehouse), ' int, ') || ' int'; I can get : Produk ID text, OFFICE int, STORE2 int Which is the column header I need but I really have no idea how to use this as column header. Anyway, If i can't do this in postgres, I will try to build sql string in the client application (Windev) and send the fixed sql to the server Thanks -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G Johnston Sent: Monday, May 05, 2014 10:25 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Crosstab function Hengky Lie wrote The crosstab warehouse column name (OFFICE Store2) is taken from tblwarehouse so when user add warehouse, crosstab column name will change automatically. And also each row has total qty. In what programming language? The only way to do this is to dynamically construct the appropriate query, with the correct number of columns, on-the-fly in the client application and send it as a normal query to the server. There is no pure SQL solution. For the total column you will need a virtual warehouse that holds those values. Likely the easiest way to get that will be to UNION ALL the main real warehouse query and another query the groups by product and sum-counts that values from the individual warehouses. IIRC you've already been shown how to write the basic crosstab query; this really isn't any different but you will need procedural logic and some way to dynamically build a SQL query string based upon how many warehouses you have at the time you run the query. I am assuming you know how to write the basic join query to get the general form needed for the real warehouse data. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Crosstab function
Are you sure that there is no pure sql solution for this ? There is no pure SQL solution because a SQL query always gives a fixed number of columns. You could compose the SQL in your client app and vary the columns by the current warehouses. Or you could say GROUP BY produkit, tblwarehouse.id and rearrange the result client-side. I'd say those are the practical approaches, but if they aren't possible, you may also be able to use Postgres's array feature, so that your result columns are: produkid warehouse_totals[] total Someone asked a similar question about a year ago, and I wrote up how to solve it with arrays and a recursive CTE here: http://www.illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/ Good luck, Paul On Mon, May 5, 2014 at 7:37 PM, Hengky Liwandouw hengkyliwand...@gmail.com wrote: Hi David, Are you sure that there is no pure sql solution for this ? I think (with my very limited postgres knowledge), function can solve this. So far i can use command: select * from crosstab ( 'select produkid, warehouseid, onhand from tblproduct order by 1', 'select distinct warehouseid from tblproduct order by 1' ) as ct (produkid text, office int, store2 int); and I have this result : PRODUKID| OFFICE | STORE2 | ---+++ 279140414 | 10 | 45 | 279140421 || 10 | The problem is the column header is static. If I have new warehouse, I should manually add it in the column header. IF I use command : select 'Produk ID text, ' || array_to_string(array(select warehousename from tblwarehouse), ' int, ') || ' int'; I can get : Produk ID text, OFFICE int, STORE2 int Which is the column header I need but I really have no idea how to use this as column header. Anyway, If i can't do this in postgres, I will try to build sql string in the client application (Windev) and send the fixed sql to the server Thanks -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G Johnston Sent: Monday, May 05, 2014 10:25 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Crosstab function Hengky Lie wrote The crosstab warehouse column name (OFFICE Store2) is taken from tblwarehouse so when user add warehouse, crosstab column name will change automatically. And also each row has total qty. In what programming language? The only way to do this is to dynamically construct the appropriate query, with the correct number of columns, on-the-fly in the client application and send it as a normal query to the server. There is no pure SQL solution. For the total column you will need a virtual warehouse that holds those values. Likely the easiest way to get that will be to UNION ALL the main real warehouse query and another query the groups by product and sum-counts that values from the individual warehouses. IIRC you've already been shown how to write the basic crosstab query; this really isn't any different but you will need procedural logic and some way to dynamically build a SQL query string based upon how many warehouses you have at the time you run the query. I am assuming you know how to write the basic join query to get the general form needed for the real warehouse data. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- _ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Crosstab function
Why not use the crosstab stuff in contrib? http://www.postgresql.org/docs/9.3/static/tablefunc.html Has it been removed or something? -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Paul Jungwirth Sent: Monday, May 5, 2014 7:49 PM To: Hengky Liwandouw Cc: pgsql-general General Subject: Re: [GENERAL] Crosstab function Are you sure that there is no pure sql solution for this ? There is no pure SQL solution because a SQL query always gives a fixed number of columns. You could compose the SQL in your client app and vary the columns by the current warehouses. Or you could say GROUP BY produkit, tblwarehouse.id and rearrange the result client-side. I'd say those are the practical approaches, but if they aren't possible, you may also be able to use Postgres's array feature, so that your result columns are: produkid warehouse_totals[] total Someone asked a similar question about a year ago, and I wrote up how to solve it with arrays and a recursive CTE here: http://www.illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/ Good luck, Paul On Mon, May 5, 2014 at 7:37 PM, Hengky Liwandouw hengkyliwand...@gmail.com wrote: Hi David, Are you sure that there is no pure sql solution for this ? I think (with my very limited postgres knowledge), function can solve this. So far i can use command: select * from crosstab ( 'select produkid, warehouseid, onhand from tblproduct order by 1', 'select distinct warehouseid from tblproduct order by 1' ) as ct (produkid text, office int, store2 int); and I have this result : PRODUKID| OFFICE | STORE2 | ---+++ 279140414 | 10 | 45 | 279140421 || 10 | The problem is the column header is static. If I have new warehouse, I should manually add it in the column header. IF I use command : select 'Produk ID text, ' || array_to_string(array(select warehousename from tblwarehouse), ' int, ') || ' int'; I can get : Produk ID text, OFFICE int, STORE2 int Which is the column header I need but I really have no idea how to use this as column header. Anyway, If i can't do this in postgres, I will try to build sql string in the client application (Windev) and send the fixed sql to the server Thanks -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G Johnston Sent: Monday, May 05, 2014 10:25 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Crosstab function Hengky Lie wrote The crosstab warehouse column name (OFFICE Store2) is taken from tblwarehouse so when user add warehouse, crosstab column name will change automatically. And also each row has total qty. In what programming language? The only way to do this is to dynamically construct the appropriate query, with the correct number of columns, on-the-fly in the client application and send it as a normal query to the server. There is no pure SQL solution. For the total column you will need a virtual warehouse that holds those values. Likely the easiest way to get that will be to UNION ALL the main real warehouse query and another query the groups by product and sum-counts that values from the individual warehouses. IIRC you've already been shown how to write the basic crosstab query; this really isn't any different but you will need procedural logic and some way to dynamically build a SQL query string based upon how many warehouses you have at the time you run the query. I am assuming you know how to write the basic join query to get the general form needed for the real warehouse data. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- _ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Crosstab function
Hengky Lie wrote Hi David, Are you sure that there is no pure sql solution for this ? I think (with my very limited postgres knowledge), function can solve this. Which is the column header I need but I really have no idea how to use this as column header. Anyway, If i can't do this in postgres, I will try to build sql string in the client application (Windev) and send the fixed sql to the server Positive. You could build the necessary string in a pl/pgsql language function but you would still have trouble getting the data out of the function the way you want; unless you output a single text column no matter the original data ( basically output a cvs version of the crosstab result). There is no dynamic execution in SQL so even though you can get a string that looks like what you want you cannot do anything with it. Only data is allowed to be dynamic; the engine has to know the names and types of all schema objects before it can start so there is no way a query can retrieve these things from its own data. It's the whole cart-horse thing... The solution is to build the query in the client and send it. Make sure you look at the various quote_ functions in order to minimize the risk of SQL injection attacks. These are especially useful for pl/pgsql functions but you might be able to use them in your first query so that you can avoid coding all the quoting and escaping rules into your application. At minimum double-quote all your identifiers and make sure there are no unescaped embedded double-quotes. If the only variables are from data in tables putting constraints on those tables would probably be useful as well - you limit valid identifiers but minimized risk of bad data causing an issue. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Crosstab-function-tp5802402p5802601.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Crosstab function
Very Clear instruction ! Thank you very much David. I will do it in my client app and follow your guidance. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G Johnston Sent: Tuesday, May 06, 2014 11:01 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Crosstab function Hengky Lie wrote Hi David, Are you sure that there is no pure sql solution for this ? I think (with my very limited postgres knowledge), function can solve this. Which is the column header I need but I really have no idea how to use this as column header. Anyway, If i can't do this in postgres, I will try to build sql string in the client application (Windev) and send the fixed sql to the server Positive. You could build the necessary string in a pl/pgsql language function but you would still have trouble getting the data out of the function the way you want; unless you output a single text column no matter the original data ( basically output a cvs version of the crosstab result). There is no dynamic execution in SQL so even though you can get a string that looks like what you want you cannot do anything with it. Only data is allowed to be dynamic; the engine has to know the names and types of all schema objects before it can start so there is no way a query can retrieve these things from its own data. It's the whole cart-horse thing... The solution is to build the query in the client and send it. Make sure you look at the various quote_ functions in order to minimize the risk of SQL injection attacks. These are especially useful for pl/pgsql functions but you might be able to use them in your first query so that you can avoid coding all the quoting and escaping rules into your application. At minimum double-quote all your identifiers and make sure there are no unescaped embedded double-quotes. If the only variables are from data in tables putting constraints on those tables would probably be useful as well - you limit valid identifiers but minimized risk of bad data causing an issue. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Crosstab-function-tp5802402p5802601. html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] crosstab function
please show - source data structures (in form of CREATE TABLE please) - actual whole query that creates duplicates 2010/12/14 Sim Zacks s...@compulab.co.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 s...@compulab.co.il mailto:s...@compulab.co.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@postgresql.org
mailto:pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] crosstab function
Just to clarify, there aren't duplicate rows. But if the customer
purchased two products there are 2 rows for that customer, one with the
quantity sum filled in for the first product and the second row with the
quantity sum filled in for the second product. I want one customer row
with all the items purchased filled in.
Sim
On 12/14/2010 12:45 PM, Sim Zacks wrote:
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 s...@compulab.co.il mailto:s...@compulab.co.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@postgresql.org
mailto:pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] crosstab function
http://www.postgresql.org/docs/8.4/interactive/tablefunc.html says:
The crosstab function produces one output row for each consecutive group
of input rows with the same row_name value. It fills the output valuecolumns,
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 crosstab itself 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.
I don't have time to verify this but I guess this is your problem, try and
test
group by customername,productname order by customername, productname
instead of
group by customername,productname order by productname
HTH
Filip
W dniu 14 grudnia 2010 11:45 użytkownik Sim Zacks s...@compulab.co.ilnapisał:
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 s...@compulab.co.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] crosstab function
That did it.It's the smallest things that always kill you.
Thanks
Sim
On 12/14/2010 05:00 PM, Filip Rembiałkowski wrote:
http://www.postgresql.org/docs/8.4/interactive/tablefunc.html says:
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.
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 |crosstab| itself 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.
I don't have time to verify this but I guess this is your problem, try
and test
group by customername,productname order by customername, productname
instead of
group by customername,productname order by productname
HTH
Filip
W dniu 14 grudnia 2010 11:45 użytkownik Sim Zacks s...@compulab.co.il
mailto:s...@compulab.co.il napisał:
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 s...@compulab.co.il mailto:s...@compulab.co.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@postgresql.org
mailto:pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] crosstab function
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] crosstab function - pivot - use column value as column description
Hy, i'm looking for a solution to get this table dynamicaly in that resultset: SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM recnokeyword WHERE r_dbrid=X r_dbrid | r_kategorie | r_descr --+-+ 14725737 |material | Alu Sorte1 14725737 |farbe | Blau 14725737 |gewicht | 100 kg material | farbe | gewicht --+-+ alu sorte 1|blau | 100kg the number of columns depends on data. is there is a simple solution for that? -- Daniel Schuchardt /Softwareentwicklung/ /http://www.prodat-sql.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] crosstab function - pivot - use column value as column description
On Tue, Jan 12, 2010 at 3:27 PM, Daniel Schuchardt d.schucha...@prodat-sql.de wrote: Hy, i'm looking for a solution to get this table dynamicaly in that resultset: SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM recnokeyword WHERE r_dbrid=X r_dbrid | r_kategorie | r_descr --+-+ 14725737 |material | Alu Sorte1 14725737 |farbe | Blau 14725737 |gewicht | 100 kg material | farbe | gewicht --+-+ alu sorte 1|blau | 100kg the number of columns depends on data. is there is a simple solution for that? have you ruled out the appropriate contrib module? http://www.postgresql.org/docs/8.4/static/tablefunc.html merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] crosstab function - pivot - use column value as column description
See bottom.
Am 12.01.2010 22:00, schrieb Merlin Moncure:
On Tue, Jan 12, 2010 at 3:27 PM, Daniel Schuchardt
d.schucha...@prodat-sql.de wrote:
Hy,
i'm looking for a solution to get this table dynamicaly in that resultset:
SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM
recnokeyword WHERE r_dbrid=X
r_dbrid | r_kategorie | r_descr
--+-+
14725737 |material | Alu Sorte1
14725737 |farbe | Blau
14725737 |gewicht | 100 kg
material | farbe | gewicht
--+-+
alu sorte 1|blau | 100kg
the number of columns depends on data.
is there is a simple solution for that?
have you ruled out the appropriate contrib module?
http://www.postgresql.org/docs/8.4/static/tablefunc.html
merlin
yes, but that function requires a return type - so i has to know how
many columns. also the column description ist defined by data but by
return type.
(
SELECT * FROM crosstab('...', '...')
AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4
text);
This will produce a result something like:
== value columns ==
row_name extra cat1 cat2 cat3 cat4
)
Daniel
--
Daniel Schuchardt
/Softwareentwicklung/
/http://www.prodat-sql.de/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] crosstab function - pivot - use column value as column description
Hello 2010/1/12 Daniel Schuchardt d.schucha...@prodat-sql.de: Hy, i'm looking for a solution to get this table dynamicaly in that resultset: SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM recnokeyword WHERE r_dbrid=X r_dbrid | r_kategorie | r_descr --+-+ 14725737 |material | Alu Sorte1 14725737 |farbe | Blau 14725737 |gewicht | 100 kg material | farbe | gewicht --+-+ alu sorte 1|blau | 100kg the number of columns depends on data. is there is a simple solution for that? -- look on http://okbob.blogspot.com/2008_08_01_archive.html Regards Pavel Stehule Daniel Schuchardt /Softwareentwicklung/ /http://www.prodat-sql.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] crosstab function - pivot - use column value as column description
pardon, correct link http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html pavel 2010/1/13 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/1/12 Daniel Schuchardt d.schucha...@prodat-sql.de: Hy, i'm looking for a solution to get this table dynamicaly in that resultset: SELECT r_dbrid, r_kategorie AS column_name, r_descr AS value FROM recnokeyword WHERE r_dbrid=X r_dbrid | r_kategorie | r_descr --+-+ 14725737 |material | Alu Sorte1 14725737 |farbe | Blau 14725737 |gewicht | 100 kg material | farbe | gewicht --+-+ alu sorte 1|blau | 100kg the number of columns depends on data. is there is a simple solution for that? -- look on http://okbob.blogspot.com/2008_08_01_archive.html Regards Pavel Stehule Daniel Schuchardt /Softwareentwicklung/ /http://www.prodat-sql.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Crosstab function Problem
Hi,
I am having problem using the
crosstab function ( which is under the contrib/tablefunc directory). I have
installed the tablefunc module and also installed the functions into my
DB.
I am using postgresql 7.4 beta
version.
I am issuing the following query,
select *from crosstab('select
path,name,valuefrom file f, metadata mwhere f.id = m.file_idand
f.type=1order by 1,2;',3)AS DP(name text,runid text, plottype
text,region text) ;
I am getting the following error
message.
No function matches the given
name and argument types. You may need to add explicit typecasts.
I also issued the given example in the tablefunc
directory and it gave the same error.
Help me out with this,
Kuldeep.
