Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 3A2BC86080B
	for <pgsql-sql@postgresql.org>; Tue, 24 Apr 2012 19:37:05 -0300 (ADT)
Received: from mailout-de.gmx.net ([213.165.64.22])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <maps.on@gmx.net>) id 1SMoM2-0008Bj-RD
	for pgsql-sql@postgresql.org; Tue, 24 Apr 2012 22:37:04 +0000
Received: (qmail invoked by alias); 24 Apr 2012 22:36:49 -0000
Received: from mue-88-130-57-017.dsl.tropolys.de (EHLO [192.168.1.113])
	[88.130.57.17]
	by mail.gmx.net (mp004) with SMTP; 25 Apr 2012 00:36:49 +0200
X-Authenticated: #14269776
X-Provags-ID: V01U2FsdGVkX1/3d9VjlEv3b4nA6duwuXHQFTnq8JZHXQnP5hGK3A
	zUjfdvHV95JZ7W
Message-ID: <4F972B12.4010509@gmx.net>
Date: Wed, 25 Apr 2012 00:37:06 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
To: Joe Conway <mail@joeconway.com>
CC: David Johnston <polobo@yahoo.com>,
	'Samuel Gendler' <sgendler@ideasculptor.com>, pgsql-sql@postgresql.org
Subject: Re: generic crosstab ?
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>
In-Reply-To: <4F972365.7070302@joeconway.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
X-Y-GMX-Trusted: 0
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201204/62
X-Sequence-Number: 36539

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
)