Message-ID: <3DCAA211.3020402@joeconway.com>
Date: Thu, 07 Nov 2002 09:25:37 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>
Cc: Christoph Haller <ch@rodos.fzk.de>, pgsql-sql@postgresql.org,
	terry@ashtonwoodshomes.com, chaudhar@umich.edu
Subject: Re: Generating a cross tab (pivot table)
References: <3DCA52E2.5529E893@rodos.fzk.de>
	<200211071215.32235.dev@archonet.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Richard Huxton wrote:
> On Thursday 07 Nov 2002 11:47 am, Christoph Haller wrote:
> 
>>Regarding to Terry's request on multiple aggregates and
>>Shahbaz's request for generating a cross tab ( pivot table )
>>in September, I've found an excellent example on a german inet page
>>http://www.itrain.de/
>>I've translated it and think it's useful for many who subscribed
>>(I hope so, maybe it was told before, but I couldn't find
>>anything about this topic in the techdecs).
> 
> Very useful. Also note there are some examples of how to produce crosstab 
> results in the table-functions contrib directory in 7.3
> 

Just to amplify a bit, in contrib/tablefunc there is a family of functions 
called crosstabN(), where N is 2, 3 and 4. These are meant as examples -- you 
could, for example, create a function crosstab5() if you need it. There is 
also a function called crosstab(), which returns type RECORD and thus requires 
the column definition to be specified in the query. See 
contrib/tablefunc/README.tablefunc for more details and examples.

These were done as relatively crude examples and therefore have some 
limitations which may or may not be a problem for you. If people find the 
functions useful and provide suggestions for improvement in functionality I'll 
try to upgrade them for 7.4.

Thanks,

Joe