X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A83835E46D4
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Aug 2004 05:06:21 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 74891-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Aug 2004 08:06:15 +0000 (GMT)
Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net
	[194.217.242.92])
	by svr1.postgresql.org (Postfix) with ESMTP id 658C75E46D2
	for <pgsql-sql@postgresql.org>; Mon, 16 Aug 2004 05:06:14 -0300 (ADT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1)
	id 1BwcVR-0001wh-0Y; Mon, 16 Aug 2004 08:06:14 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id AD63018551; Mon, 16 Aug 2004 09:06:05 +0100 (BST)
Message-ID: <41206AF0.7020001@archonet.com>
Date: Mon, 16 Aug 2004 09:06:08 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.7 (X11/20040615)
X-Accept-Language: en-us, en
MIME-Version: 1.0
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
References: <82E30406384FFB44AFD1012BAB230B55037D0504@shiva.au.lpint.net>
In-Reply-To: <82E30406384FFB44AFD1012BAB230B55037D0504@shiva.au.lpint.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=PLING_QUERY
X-Spam-Level: 
X-Archive-Number: 200408/143
X-Sequence-Number: 18640

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