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

Re: pgsql crosstab function

From: Joe Conway <mail(at)joeconway(dot)com>
To: Fabrizio Mazzoni <veramente(at)libero(dot)it>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pgsql crosstab function
Date: 2003-11-03 03:32:57
Message-ID: 3FA5CC69.7000704@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Fabrizio Mazzoni wrote:
> Hello all .. i just wrote this script in plpgsql. Basically it
> performs a crosstab query. The difference between this and the one
> you already have in contrib is that this one has no limitations on
> the arguments passed to it.

I don't understand what you think is different. Your example can be 
produced using contrib/tablefunc/crosstab like so:

regression=# select art, coalesce(s,0) as "S", coalesce(m,0) as "M", 
coalesce(l,0) as "L", coalesce(xl,0) as "XL", coalesce(xxl,0) as "XXL" 
from crosstab('select art, tgl, sum(qty) from art group by art, tgl 
order by 1', 'select ''L'' union all select ''M'' union all select ''S'' 
union all select ''XL'' union all select ''XXL''') as (art varchar, L 
int, M int, S int, XL int, XXL int);
    art    | S  | M  | L  | XL | XXL
----------+----+----+----+----+-----
  508301   |  0 |  0 | 10 | 38 |   0
  5001001  |  0 | 12 |  0 |  0 |  25
  45370104 | 10 |  0 |  0 |  0 |   0
(3 rows)

Or with nulls in place of zeros, like this:

regression=# select * from crosstab('select art, tgl, sum(qty) from art 
group by art, tgl order by 1', 'select ''S'' union all select ''M'' 
union all select ''L'' union all select ''XL'' union all select 
''XXL''') as (art varchar, L int, M int, S int, XL int, XXL int); 
 
                                art    | l  | m  | s  | xl | xxl
----------+----+----+----+----+-----
  508301   |    |    | 10 | 38 |
  5001001  |    | 12 |    |    |  25
  45370104 | 10 |    |    |    |
(3 rows)

Joe



In response to

pgsql-hackers by date

Next:From: Marc G. FournierDate: 2003-11-03 05:38:54
Subject: 7.4RC1 tag'd, branched and bundled ...
Previous:From: Gaetano MendolaDate: 2003-11-03 01:08:18
Subject: Re: Experimental patch for inter-page delay in VACUUM

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