From: Andreas <maps.on@gmx.net>
To: Joe Conway <mail@joeconway.com>
Cc: David Johnston <polobo@yahoo.com>
Cc: Samuel Gendler <sgendler@ideasculptor.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: generic crosstab ?
Date: Wed, 25 Apr 2012 00:37:06 +0200
Message-ID: <4F972B12.4010509@gmx.net> (raw)
In-Reply-To: <4F972365.7070302@joeconway.com>
References: <4F97069B.7020303@gmx.net>
<CAEV0TzBMbQo5+HZ8B+JgFG9Z7-G6hP_hW9E2R4yor2oKNmeYHA@mail.gmail.com>
<4F971C7F.7050803@gmx.net>
<04d701cd2263$323e1ad0$96ba5070$@yahoo.com>
<4F972365.7070302@joeconway.com>
Am 25.04.2012 00:04, schrieb Joe Conway:
> On 04/24/2012 02:42 PM, David Johnston wrote:
>> You must specify the output record structure:
>>
>> SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name
>> colN_type]* )
>>
>> Whether this relates to the “materialization node” message you are
>> receiving I have no idea.
> The error is because you are selecting from a set returning function in
> the target list rather than the from clause. It should be more like:
>
> SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name
>> colN_type]* )
>
OK now i get at least some result.
But
1) I need to know how many categories will apear to construct the target
list. This is a wee bit of a problem as this number is actually dynamic.
2) There are some rows in the resulting list with empty columns within
the row.
When I execute the first query for a parent ID that has gaps in the
crosstab I see it shows no gaps in the categories when called outside
crosstab().
E.g. it dumps x1, x2, x3, x4, x5 when called seperately but crosstab()
shows
x1, x2, null, null, x5, null, x6, x7
How does this make sense ?
Thanks for the answers so far :)
select *
from
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, 15) as category order by 1
$$
)
as result (
row_name integer,
x1 integer,
x2 integer,
x3 integer,
x4 integer,
x5 integer,
x6 integer,
x7 integer,
x8 integer,
x9 integer,
x10 integer,
x11 integer,
x12 integer,
x13 integer,
x14 integer,
x15 integer
)
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, mail@joeconway.com, polobo@yahoo.com, sgendler@ideasculptor.com
Subject: Re: generic crosstab ?
In-Reply-To: <4F972B12.4010509@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