Hi,
I was just tying to perform a crosstab query on pgsql 9.1.4.
I wonder if there is something to know for creating a pivot table by using
crosstab when the pivot table has more then ten fields.
I prepared crosstab query that aggregate less than 10 fields correctly whilst
another crosstab qu
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
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
: 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
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 c
, 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
n (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
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 & St
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
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 tabl
you provide inadequate information to provide anything more than a guess...
Are you sure your issue isn't simply a client display consideration -
basically word-wrapping?
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/crosstab-creating-multiple-rows-for-same
Hi,
I am using the crosstab function in Postgresql 9.0.
The functions works fine when the distinct category value is less. But when
it goes to to higher like 100 or more, it creates multiple rows for the
same rowid.
Any idea about it?
Thanks.
Excellent :)
Thanks - looks as acceptable workaround...
Many thanks,
Misa
2012/9/5 Joe Conway
> On 09/04/2012 04:45 PM, Misa Simic wrote:
> > Hi Joe,
> >
> > Do you maybe know, is it possible inside PL/R to call another PL/R
> > function - but take result as R object (whatever R function retu
On 09/04/2012 04:45 PM, Misa Simic wrote:
> Hi Joe,
>
> Do you maybe know, is it possible inside PL/R to call another PL/R
> function - but take result as R object (whatever R function returns)?
Mmmm, what about using plr_modules table?
It creates native R functions that get installed into your
>
> (what btw forced me to check it in plv8 - and it seems they use the same
> approach internally in behind :( - though doesn't make sense because of
> find_function works just with plv8 postgres functions)
Just realised I have called wrong method - so above is not true... it
returns real v8 ob
Hi Joe,
Do you maybe know, is it possible inside PL/R to call another PL/R function
- but take result as R object (whatever R function returns)?
If we take this scenario for example (Take some data from DB, pivot them,
and save it as CSV)... pseudo code would be:
-Execute SQL query
-pivot return
On 09/04/2012 02:59 PM, Aram Fingal wrote:
> On Sep 4, 2012, at 4:36 PM, A.M. wrote:
>
>> Or you could return the heatmap/plot as BYTEA data or use arrays as
>> necessary.
>
> I was actually thinking exactly the same thing. Part of the reason I
> use PostgreSQL for all my bioinformatics work is
On Sep 4, 2012, at 4:36 PM, A.M. wrote:
> Or you could return the heatmap/plot as BYTEA data or use arrays as necessary.
I was actually thinking exactly the same thing. Part of the reason I use
PostgreSQL for all my bioinformatics work is that there is a need to correctly
associate analysis r
On 09/04/2012 01:23 PM, Aram Fingal wrote:
> So then, PL/R is not a solution to being able to pivot tables
> directly in PostgreSQL but I might be able to define a PL/R procedure
> which, for example, pivots tables and then uses the write.table()
> function of R to send the results to disk without
On Tue, Sep 4, 2012 at 10:39 AM, punnoose
wrote:
> hi all
> How could i use crostab to display variable number of columns. in the output
> There could be variable number of columns
> Regards
> Punnoose
No. The workaround I use is to write a query generator in pl/pgsql
(you can also do it in the
That is correct,
Re you additional question re PL/R or get data from Pg in plain R - I am
not sure it will be that much faster inside PL/R...
I dont think there will be too much difference in execution time... Would
be good to test it... and would be good to share results with us :)
Thanks,
Mis
On Sep 4, 2012, at 4:30 PM, Aram Fingal wrote:
>
> On Sep 4, 2012, at 4:18 PM, Misa Simic wrote:
>
>> Inside PL/R you can take the same table as it is (unpivoted) as your
>> data.frame and then pivot it inside R using reshape package,,, And then
>> inside PL/R function do whatever you would l
On Sep 4, 2012, at 4:18 PM, Misa Simic wrote:
> Inside PL/R you can take the same table as it is (unpivoted) as your
> data.frame and then pivot it inside R using reshape package,,, And then
> inside PL/R function do whatever you would like to do with data i.e export to
> whatever... - but you
No,
you must use AS (..) in crosstab query...
To actually, be able to create AS on "unkown" data in design time, what
should produce an "unknown" result and unknown number of columns we are
using Dynamic SQL to build AS part... (but of course function can't return
result - just for export to csv
On Sep 4, 2012, at 3:56 PM, Joe Conway wrote:
> On 09/04/2012 12:48 PM, Aram Fingal wrote:
>> So, are you saying that if I do something like this:
>>
>> copy(crosstab(source_sql, category_sql)) to '/output.csv' with csv;
>>
>> Then I don't have to list what the columns are going to be? In othe
On 09/04/2012 12:48 PM, Aram Fingal wrote:
> So, are you saying that if I do something like this:
>
> copy(crosstab(source_sql, category_sql)) to '/output.csv' with csv;
>
> Then I don't have to list what the columns are going to be? In other
> words, I can skip the "AS (...)" clause which is sh
On Sep 4, 2012, at 3:26 PM, Joe Conway wrote:
> On 09/04/2012 12:17 PM, Aram Fingal wrote:
>> On Sep 4, 2012, at 2:51 PM, Vincent Veyron wrote:
>>> see the documentation for Additional Supplied Modules, in your
>>> case tablefunc :
>>>
>>> http://www.postgresql.org/docs/9.1/static/tablefunc.html
On 09/04/2012 12:17 PM, Aram Fingal wrote:
> On Sep 4, 2012, at 2:51 PM, Vincent Veyron wrote:
>> see the documentation for Additional Supplied Modules, in your
>> case tablefunc :
>>
>> http://www.postgresql.org/docs/9.1/static/tablefunc.html
>
> I evaluated tablefunc about a year and a half ago
On Sep 4, 2012, at 2:51 PM, Vincent Veyron wrote:
> Le mardi 04 septembre 2012 à 08:39 -0700, punnoose a écrit :
>> hi all
>> How could i use crostab to display variable number of columns. in the output
>> There could be variable number of columns
>
> see the documentation for Additional Supplie
This is really good feature (crostab) to implement ..
Best Regards,
Dinesh
manojadinesh.blogspot.com
On Wed, Sep 5, 2012 at 12:21 AM, Vincent Veyron wrote:
> Le mardi 04 septembre 2012 à 08:39 -0700, punnoose a écrit :
> > hi all
> > How could i use crostab to display variable number of column
Le mardi 04 septembre 2012 à 08:39 -0700, punnoose a écrit :
> hi all
> How could i use crostab to display variable number of columns. in the output
> There could be variable number of columns
see the documentation for Additional Supplied Modules, in your case
tablefunc :
http://www.postgresql.o
Has anyone tried developing a PLR procedure to do the equivalent of crosstab
but using the Reshape library from R? The reason I ask is that I have been
extracting tables like this with an R script, using RPostgreSQL, then functions
from Reshape and writing the output to a .csv file. I wonder i
Hi ,
I believe, we need to have a Dynamic SQL for this to generate the dynamic
Crostab.. I have tried the same earlier and wrote the below Calendar Query
for the year 2011 including holidays.
Crostab Example
=
select
min(case when extract(month from d.dd)= 1 then
'Jan'
when extract(mo
hi all
How could i use crostab to display variable number of columns. in the output
There could be variable number of columns
Regards
Punnoose
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/crosstab-tp5722632.html
Sent from the PostgreSQL - general mailing list archiv
On Monday 17 January 2011 07:21:11 you wrote:
> Am 17.01.2011 00:20, schrieb Steve Litt:
> > On Sunday 16 January 2011 17:40:34 Julia Jacobson wrote:
> >> Hello everybody out there using PostgreSQL,
> >>
> >> A table with the results of students in different exams
> >>
> >> student | date_of_exam |
> -Original Message-
> From: Julia Jacobson [mailto:julia.jacob...@arcor.de]
> Sent: Monday, January 17, 2011 7:21 AM
> To: pgsql-general@postgresql.org
> Cc: Steve Litt
> Subject: Re: Crosstab query on huge amount of values
>
> Am 17.01.2011 00:20, schrieb Steve Litt:
> > On Sunday 16
Am 17.01.2011 00:20, schrieb Steve Litt:
On Sunday 16 January 2011 17:40:34 Julia Jacobson wrote:
Hello everybody out there using PostgreSQL,
A table with the results of students in different exams
student | date_of_exam | grade
--
Peter | 2010-09-09 | 2
Tom
On Sunday 16 January 2011 17:40:34 Julia Jacobson wrote:
> Hello everybody out there using PostgreSQL,
>
> A table with the results of students in different exams
>
> student | date_of_exam | grade
> --
> Peter | 2010-09-09 | 2
> Tom | 2010-09-09 | 1
> Andy
Hello everybody out there using PostgreSQL,
A table with the results of students in different exams
student | date_of_exam | grade
--
Peter | 2010-09-09 | 2
Tom | 2010-09-09 | 1
Andy| 2010-09-21 | 3
Tom | 2010-09-21 | 4
Peter | 2010-09-21 | 1
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 wit
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 fe
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
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,
please show
- source data structures (in form of CREATE TABLE please)
- actual whole query that creates duplicates
2010/12/14 Sim Zacks
> 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 pro
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 prod
Merlin Moncure writes:
> On Wed, Mar 10, 2010 at 7:00 PM, Amol Chiplunkar
> wrote:
>> My postgres 8.1.4 installation does not have the crosstab
>> functions. Most likely due to unavailability of contrib/tablefunc
>> module.
> does too!
I think the problem is the OP didn't install postgresql-con
On Wed, Mar 10, 2010 at 7:00 PM, Amol Chiplunkar
wrote:
> Hi,
>
> My postgres 8.1.4 installation does not have the crosstab
> functions. Most likely due to unavailability of contrib/tablefunc
> module.
does too!
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/tablefunc/?only_with_tag=REL8_
Hi,
My postgres 8.1.4 installation does not have the crosstab
functions. Most likely due to unavailability of contrib/tablefunc
module.
Is there a std version of tablefunc .so that can be downloaded and used ?
thanks
- Amol
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
pardon,
correct link
http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
pavel
2010/1/13 Pavel Stehule :
> Hello
>
> 2010/1/12 Daniel Schuchardt :
>> Hy,
>>
>> i'm looking for a solution to get this table dynamicaly in that resultset:
>>
>> SELECT r_dbrid, r_kategorie AS c
Hello
2010/1/12 Daniel Schuchardt :
> 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
> --+-+
See bottom.
Am 12.01.2010 22:00, schrieb Merlin Moncure:
On Tue, Jan 12, 2010 at 3:27 PM, Daniel Schuchardt
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=
On Tue, Jan 12, 2010 at 3:27 PM, Daniel Schuchardt
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
> --+
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 |far
Good day... I'm new in postgresql and I used PostgreSQL 8.2. I tried
generating a query that can convert your rows into columns. I was
happy when I discovered the crosstab function. But when I tried
producing reports with more than 17 columns it displayed the following
error message:
ERROR: invali
Il Thursday 25 October 2007 16:29:33 Scott Marlowe ha scritto:
> On 10/24/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> > Joe Conway <[EMAIL PROTECTED]> writes:
> > > Tom Lane wrote:
> > >> 1. Treat NULL rowid as a category in its own right. This would
> > >> conform with the behavior of GROUP BY and
On 10/24/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> Joe Conway <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> 1. Treat NULL rowid as a category in its own right. This would conform
> >> with the behavior of GROUP BY and DISTINCT, for instance.
>
> > In any case, the attached changes the behav
Joe Conway <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> 1. Treat NULL rowid as a category in its own right. This would conform
>> with the behavior of GROUP BY and DISTINCT, for instance.
> In any case, the attached changes the behavior to #1 for both flavors of
> crosstab (the original cros
Tom Lane wrote:
Jorge Godoy <[EMAIL PROTECTED]> writes:
Em Thursday 18 October 2007 16:37:59 Joe Conway escreveu:
The row is pretty useless without a rowid in this context -- it seems
like the best thing to do would be to skip those rows entirely. Of
course you could argue I suppose that it oug
"Tom Lane" <[EMAIL PROTECTED]> writes:
> 3. Throw a NOTICE or WARNING (hopefully only one message not repeated
> ones) if NULL rowid is seen, then ignore the row.
>From my experience with OLTP I don't like this one. A warning for DML is
effectively the same as an error if you're running thousands
But when re-doing the query now without the JOIN, it works (almost):
SELECT
*
FROM
crosstab(
'SELECT
id_country AS id,
year_start AS year,
value
FROM
agri_area AS d
WHERE
year_start = 2003 OR year_start = 2
Tom Lane wrote:
Jorge Godoy <[EMAIL PROTECTED]> writes:
Em Thursday 18 October 2007 16:37:59 Joe Conway escreveu:
The row is pretty useless without a rowid in this context -- it seems
like the best thing to do would be to skip those rows entirely. Of
course you could argue I suppose that it oug
Jorge Godoy <[EMAIL PROTECTED]> writes:
> Em Thursday 18 October 2007 16:37:59 Joe Conway escreveu:
>> The row is pretty useless without a rowid in this context -- it seems
>> like the best thing to do would be to skip those rows entirely. Of
>> course you could argue I suppose that it ought to thr
Em Thursday 18 October 2007 16:37:59 Joe Conway escreveu:
> Tom Lane wrote:
> > so it's trying to pstrdup a null result from SPI_getvalue.
> >
> > Obviously it shouldn't crash, but I'm not sure what it *should* do in
> > this case. Joe?
>
> The row is pretty useless without a rowid in this context
Tom Lane wrote:
so it's trying to pstrdup a null result from SPI_getvalue.
Obviously it shouldn't crash, but I'm not sure what it *should* do in
this case. Joe?
The row is pretty useless without a rowid in this context -- it seems
like the best thing to do would be to skip those rows entirel
On 10/18/07, Stefan Schwarzer <[EMAIL PROTECTED]> wrote:
> But when re-doing the query now without the JOIN, it works (almost):
>
> SELECT
> *
> FROM
> crosstab(
>'SELECT
> id_country AS id,
> year_start AS year,
> value
> FROM
>
On 10/18/07, Stefan Schwarzer <[EMAIL PROTECTED]> wrote:
> > Could you provide a self-contained test case for this? There's not
> > really enough information here for someone else to duplicate the
> > problem. Also, which PG version are you using?
>
> Wasn't sure what you ment with "a self contai
Stefan Schwarzer <[EMAIL PROTECTED]> writes:
> Here is a SQL dump for the table. One can just neglect the JOIN with
> the countries table (which just replaces the country id with the
> country name):
> http://geodata.grid.unep.ch/download/sql_agri_area.sql.zip
> But when re-doing the query now
Could you provide a self-contained test case for this? There's not
really enough information here for someone else to duplicate the
problem. Also, which PG version are you using?
Wasn't sure what you ment with "a self containted test case". Is it
the raw data?
Here is a SQL dump for the ta
Stefan Schwarzer <[EMAIL PROTECTED]> writes:
> I had a couple of problems getting there. But now that I have the
> feeling that this is OK, it tells me this:
> server closed the connection unexpectedly
Could you provide a self-contained test case for this? There's not
really enough information
Hi there,
successfully installed the tablefunc package.
Now, I would like to transform this kind of result based on a normal
SQL:
c_name |year|value
---
Germany | 2001| 123
Germany | 2002| 125
Germany
On Aug 8, 2007, at 19:23 , David Blewett wrote:
Since the number of questions to be pivoted will vary per
questionnaire the method should accept a number of columns
parameter. The crosstab in the tablefunc contrib module expects the
columns to be explicitly identified before hand. I suppose it
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256
Hi All:
I'm building an application along the lines of the questionnaire
design on varlena.com [1], with slight modifications. In my design,
there's a table called chartversionquestion that collects different
questions into a bundle to create the qu
I am trying to use the crosstab function of the contrib tablefunc.
Reading the README, I believe I am supposed to be using crosstab(sql, N)
for my situation and wondering if the SQL can be based on a view? I have
this view created that gives me each sales rep and their total number of
units sold an
Hi,
I will need to create a crosstab representation of my resultset.
The resultset looks like this: rowid, columnid, cellvalue
I don't know beforehand the number of columns and their id's but they
will be close to 200.
I have looked at the crosstab tablefunction but it seems that you have
to know
On Fri, Nov 25, 2005 at 03:09:06PM +0100, Vittorio wrote:
> Dear all,
> crosstab doesn't seem to work
> anymore putting the above select into crosstab
>
> switch=# select * from
> crosstab('select rowid,attribute, sum(value) as val from ct1 group by
> rowid, attribute order by 1,2;', 2) as c
Dear all,
I installed postgresql 8.04 on a pentium box with freebsd
5.4
# psql --version
psql (PostgreSQL) 8.0.4
In the db switch I have
the following ct1 table
switch=# select * from ct1 order by 1,2;
id |
rowclass | rowid | attribute | value
+--+---+---
+---
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('s
--- Joe Conway <[EMAIL PROTECTED]> wrote:
>
> Just for info, there is a (C language) crosstab
> function in
> contrib/tablefunc.
>
> The one in 7.3 has a serious limitation (the source
> query must ensure
> that for each row in the crosstab, there is a record
> representing each
> column of th
> > > Is it possible to perform a crosstab query in postgres similar the
> > > functionality that MS Access provides?
> > >
> > > I tried building the query in Access (against postgre 6.5.3 using ODBC)
> > > and using the SQL created by Access, but it looks like very non-standard
> > > SQL code an
79 matches
Mail list logo
