From: David Johnston <polobo@yahoo.com>
To: 'Andreas' <maps.on@gmx.net>
To: 'Samuel Gendler' <sgendler@ideasculptor.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: generic crosstab ?
Date: Tue, 24 Apr 2012 17:42:49 -0400
Message-ID: <04d701cd2263$323e1ad0$96ba5070$@yahoo.com> (raw)
In-Reply-To: <4F971C7F.7050803@gmx.net>
References: <4F97069B.7020303@gmx.net>
<CAEV0TzBMbQo5+HZ8B+JgFG9Z7-G6hP_hW9E2R4yor2oKNmeYHA@mail.gmail.com>
<4F971C7F.7050803@gmx.net>
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Andreas
Sent: Tuesday, April 24, 2012 5:35 PM
To: Samuel Gendler
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] generic crosstab ?
Am 24.04.2012 22:08, schrieb Samuel Gendler:
On Tue, Apr 24, 2012 at 1:01 PM, Andreas <maps.on@gmx.net> wrote:
Hi,
is there a generic solution to dump the result of a query as a crosstab,
when I can't know how many columns I will need?
E.g. I get something like this:
id, x
1, a
1, b
1, c
2, l
2, m
Yes. You can provide a query which returns the columns to the version of
the crosstab function which looks like this:
crosstab(text source_sql, text category_sql)
It does exactly what you are looking for. The second query returns the set
of values that act as columns in the final result (the pivot for each row in
the result returned by the first query). This allows the function to
correctly insert a null for any column for which there is no row in the
first query results.
I got stuck with an error that translates to "Materialisation mode is needed
but is not allowed in this context."
I couldn't figure out what this materialisation mode is, yet.
Could you please have a look at my query sample?
Both queries work for themselves but crosstab() fails. :(
I checked and there are never more than 20 child_ids per parent_id so there
should be enough columns.
select
crosstab (
$$
select
parent_id as row_name,
'x' || row_number() over ( partition by parent_id order by child_id
) as category,
child_id as value
from
children
order by 1
$$,
$$
select 'x' || generate_series(1, 20) as cat order by 1
$$
);
You must specify the output record structure:
SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name
colN_type]* )
See: http://www.postgresql.org/docs/9.0/interactive/tablefunc.html for
official usage and examples
Whether this relates to the "materialization node" message you are receiving
I have no idea.
Dave
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: polobo@yahoo.com, maps.on@gmx.net, sgendler@ideasculptor.com
Subject: Re: generic crosstab ?
In-Reply-To: <04d701cd2263$323e1ad0$96ba5070$@yahoo.com>
* 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