From: Richard Huxton <dev@archonet.com>
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: CROSS-TAB query help? I have read it cant be done in one
Date: Mon, 16 Aug 2004 09:06:08 +0100
Message-ID: <41206AF0.7020001@archonet.com> (raw)
In-Reply-To: <82E30406384FFB44AFD1012BAB230B55037D0504@shiva.au.lpint.net>
References: <82E30406384FFB44AFD1012BAB230B55037D0504@shiva.au.lpint.net>
Theo Galanakis wrote:
> Does anyone know how to perform a cross-tab query in ONE SQL without having
> to write a SP? The SQL at the end of this email attempts to display the
> subquery result-set in a cross-tab format, it does not group the content
> onto one row as it should in the sample below. SQL is below if it makes any
> sense, however the sub-query returns data as below.
>
> Examle:
>
> Name Value
> ID 1
> Cola 10
> Colb 20
> Colc 30
> Cold 40
> Cole 50
>
> I want to output as:
>
> ID, cola, colb, colb, cold, cole
> 1 10 30 30 40 50
> Actual Output:
>
> content_object_id | xpos | ypos | text | textangle | texttype
> | symbol | linktype
> -------------------+------+------+-------------------+-----------+----------
> 100473 | 93 | | | |
> 100473 | | 77 | | |
> 100473 | | | text1 | |
Don't forget the provided crosstab functions (in contrib/). If you don't
want that, you could aggregate your results:
SELECT content_object_id, MAX(xpos), MAX(ypos), ...
FROM (
<your query here>
) AS raw
GROUP BY content_object_id;
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, Theo.Galanakis@lonelyplanet.com.au
Subject: Re: CROSS-TAB query help? I have read it cant be done in one
In-Reply-To: <41206AF0.7020001@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox