Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 23A1486080B for ; Tue, 24 Apr 2012 18:43:35 -0300 (ADT) Received: from smtp101.prem.mail.ac4.yahoo.com ([76.13.13.40]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SMnWG-0000Fq-Ft for pgsql-sql@postgresql.org; Tue, 24 Apr 2012 21:43:34 +0000 Received: (qmail 50135 invoked from network); 24 Apr 2012 21:43:18 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:Cc:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Content-Language:Thread-Index; b=Y4UgswGJ/hznh+uZWe5z6kqectG3vpVQSqec9rnR/DKVOBA8uA/oCDh9YBasny82dGjDcHmhBoRYWa71xWBPJqvcfxfKB7HmfuOMvTsyid6Yha9nlEQPIAdJv34dBvY4Jc5MZreIpDwKZ0eRzKkm6E2RWEvgB1zJlSffrvs+has= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1335303798; bh=2x1TJKkhpNHO+z+0J+CSpQjG5axexdb96dZdnDxtAE0=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:Cc:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Content-Language:Thread-Index; b=I1FlG8hRFxZkGHklF+31MwiAvNpARjBuaKbM1782K46TLBvfj5AwpVu88PpYAjhKBMAoHVWCiKCWXwrbcygBaqgNFKeaonN9T4bh7nd/vMwbar8SJPhuj9VqhyR7TQEzbqbrz5EY89XP1T+iHN1LTMSJh7RTbEMzvVfqCSXDHOY= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: Ew1mJ.QVM1lEZ09VqGji4103GqgI8IcUVcY_m5aQhLubVYb b5iOC7M07lcM9_lUsBl.u8Q7qA8LZ0VQJFM.HbsqlhheW699YlZJ4ZVfjxwV qcq3F0Evv5SkN5bPpwacT5lqS3mKm3ytOj6abb6e8.WGnw9dz2aw4Fyb7hze TMpcJOG8LC_IcUGovIFiZvUFA0dfsggMlXkZ57JdQ0uayP7TsqD_SMb4S2J8 s3GDF.4qqKXvmqlcqFb2jjoQNrRArRRMoC7euvp5f7yZtkHls33yFHKg.3oP Es0HG9LmUjj93OYkPTUCtFsgnNst9hPdQ2F8AcqoO._CNa5PHyUOq12pnGvf 9BzM.tUXOE5TgmlkClPfZgwmpxOEdXyWi5bClrpEz5jaU.jvT8W8RbUtKDDk vI1xynPE93IXMkV_dQCPs X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from WolfDog (polobo@24.93.23.188 with login) by smtp101.prem.mail.ac4.yahoo.com with SMTP; 24 Apr 2012 14:43:18 -0700 PDT From: "David Johnston" To: "'Andreas'" , "'Samuel Gendler'" Cc: References: <4F97069B.7020303@gmx.net> <4F971C7F.7050803@gmx.net> In-Reply-To: <4F971C7F.7050803@gmx.net> Subject: Re: generic crosstab ? Date: Tue, 24 Apr 2012 17:42:49 -0400 Message-ID: <04d701cd2263$323e1ad0$96ba5070$@yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_04D8_01CD2241.AB2E9DB0" X-Mailer: Microsoft Outlook 14.0 Content-Language: en-us Thread-Index: AQLYBUiVtUNNbmcCtipnT+FzvkS2+AH8HLoUAutzCeuUbX1u0A== X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/60 X-Sequence-Number: 36537 This is a multipart message in MIME format. ------=_NextPart_000_04D8_01CD2241.AB2E9DB0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Sent: Tuesday, April 24, 2012 5:35 PM To: Samuel Gendler Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] generic crosstab ? 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 $$ ); You must specify the output record structure: SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name colN_type]* ) See: http://www.postgresql.org/docs/9.0/interactive/tablefunc.html for official usage and examples Whether this relates to the "materialization node" message you are receiving I have no idea. Dave ------=_NextPart_000_04D8_01CD2241.AB2E9DB0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of = Andreas
Sent: Tuesday, April 24, 2012 5:35 = PM
To: Samuel Gendler
Cc: = pgsql-sql@postgresql.org
Subject: Re: [SQL] generic crosstab = ?

 

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 =             &= nbsp;      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
$$
);

 

 

You must specify the output record structure:

 

SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name = colN_type]* )

 

See: ht= tp://www.postgresql.org/docs/9.0/interactive/tablefunc.html for = official usage and examples

 

Whether this relates to the “materialization node” = message you are receiving I have no idea.

 

Dave

 

------=_NextPart_000_04D8_01CD2241.AB2E9DB0--