Skip site navigation (1) Skip section navigation (2)

Re: generic crosstab ?

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: Samuel Gendler <sgendler(at)ideasculptor(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: generic crosstab ?
Date: 2012-04-24 21:34:55
Message-ID: 4F971C7F.7050803@gmx.net (view raw or flat)
Thread:
Lists: pgsql-sql
Am 24.04.2012 22:08, schrieb Samuel Gendler:
>
>
> On Tue, Apr 24, 2012 at 1:01 PM, Andreas <maps(dot)on(at)gmx(dot)net 
> <mailto:maps(dot)on(at)gmx(dot)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
$$
);

In response to

Responses

pgsql-sql by date

Next:From: David JohnstonDate: 2012-04-24 21:42:49
Subject: Re: generic crosstab ?
Previous:From: Samuel GendlerDate: 2012-04-24 20:08:23
Subject: Re: generic crosstab ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group