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

Re: generic crosstab ?

From: Samuel Gendler <sgendler(at)ideasculptor(dot)com>
To: Andreas <maps(dot)on(at)gmx(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: generic crosstab ?
Date: 2012-04-24 20:08:23
Message-ID: CAEV0TzBMbQo5+HZ8B+JgFG9Z7-G6hP_hW9E2R4yor2oKNmeYHA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Tue, Apr 24, 2012 at 1:01 PM, Andreas <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.

In response to

Responses

pgsql-sql by date

Next:From: AndreasDate: 2012-04-24 21:34:55
Subject: Re: generic crosstab ?
Previous:From: AndreasDate: 2012-04-24 20:01:31
Subject: generic crosstab ?

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