From: 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 23:34:55 +0200
Message-ID: <4F971C7F.7050803@gmx.net> (raw)
In-Reply-To: <CAEV0TzBMbQo5+HZ8B+JgFG9Z7-G6hP_hW9E2R4yor2oKNmeYHA@mail.gmail.com>
References: <4F97069B.7020303@gmx.net>
<CAEV0TzBMbQo5+HZ8B+JgFG9Z7-G6hP_hW9E2R4yor2oKNmeYHA@mail.gmail.com>
Am 24.04.2012 22:08, schrieb Samuel Gendler:
>
>
> On Tue, Apr 24, 2012 at 1:01 PM, Andreas <maps.on@gmx.net
> <mailto: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
$$
);
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: maps.on@gmx.net, sgendler@ideasculptor.com
Subject: Re: generic crosstab ?
In-Reply-To: <4F971C7F.7050803@gmx.net>
* 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