Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 0AE9F86080B for ; Tue, 24 Apr 2012 18:35:09 -0300 (ADT) Received: from mailout-de.gmx.net ([213.165.64.23]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SMnO6-0007Ci-Jq for pgsql-sql@postgresql.org; Tue, 24 Apr 2012 21:35:08 +0000 Received: (qmail invoked by alias); 24 Apr 2012 21:34:53 -0000 Received: from mue-88-130-57-017.dsl.tropolys.de (EHLO [192.168.1.113]) [88.130.57.17] by mail.gmx.net (mp035) with SMTP; 24 Apr 2012 23:34:53 +0200 X-Authenticated: #14269776 X-Provags-ID: V01U2FsdGVkX1/QTjQ7QBjQTotQy6FSkN8ueVuPttzSJG4n7BqxGj IUmJ8KC1gx6KEM Message-ID: <4F971C7F.7050803@gmx.net> Date: Tue, 24 Apr 2012 23:34:55 +0200 From: Andreas User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:11.0) Gecko/20120327 Thunderbird/11.0.1 MIME-Version: 1.0 To: Samuel Gendler CC: pgsql-sql@postgresql.org Subject: Re: generic crosstab ? References: <4F97069B.7020303@gmx.net> In-Reply-To: Content-Type: multipart/alternative; boundary="------------050108030201030403030502" X-Y-GMX-Trusted: 0 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/59 X-Sequence-Number: 36536 This is a multi-part message in MIME format. --------------050108030201030403030502 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Am 24.04.2012 22:08, schrieb Samuel Gendler: > > > On Tue, Apr 24, 2012 at 1:01 PM, Andreas > 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 $$ ); --------------050108030201030403030502 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
$$
);
--------------050108030201030403030502--