Re: Column as arrays.. more efficient than columns? - Mailing list pgsql-general

Ow Mun Heng wrote:
> On Thu, 2007-09-06 at 20:19 -0700, Joe Conway wrote:
>> An alternative way to get the output below, would be to feed your
>> aggregate query above to the crosstab() function in contrib/tablefunc.
>
> I just looked at it and seems like the
> ...
> row_name and value must be of type text

It doesn't say that, and apparently you didn't bother to test...

create table foo (number int, subset int, value int);
INSERT INTO foo VALUES(1,1,11),(1,2,22),(1,3,30),(1,4,43);
INSERT INTO foo VALUES(2,1,10),(2,2,23),(2,3,31),(2,4,46);
INSERT INTO foo VALUES(3,1,12),(3,2,24),(3,3,34),(3,4,47);
INSERT INTO foo VALUES(4,1,9),(4,2,23),(4,3,35),(4,4,42);
INSERT INTO foo VALUES(5,1,10),(5,2,22),(5,3,33),(5,4,45);

select number, round(avg(case when subset = '0' then value else null
end),0) as v0,
round(avg(case when subset = '1' then value else null end),0) as v1,
round(avg(case when subset = '2' then value else null end),0) as v2,
round(avg(case when subset = '3' then value else null end),0) as v3,
round(avg(case when subset = '4' then value else null end),0) as v4
from foo
group by number order by number;

  number | v0 | v1 | v2 | v3 | v4
--------+----+----+----+----+----
       1 |    | 11 | 22 | 30 | 43
       2 |    | 10 | 23 | 31 | 46
       3 |    | 12 | 24 | 34 | 47
       4 |    |  9 | 23 | 35 | 42
       5 |    | 10 | 22 | 33 | 45
(5 rows)

select * from crosstab(
   'select number, subset, round(avg(value))
    from foo group by number, subset order by number',
   'select * from (values(0),(1),(2),(3),(4)) as vc')
AS ct(code int, v0 int, v1 int, v2 int, v3 int, v4 int);

  code | v0 | v1 | v2 | v3 | v4
------+----+----+----+----+----
     1 |    | 11 | 22 | 30 | 43
     2 |    | 10 | 23 | 31 | 46
     3 |    | 12 | 24 | 34 | 47
     4 |    |  9 | 23 | 35 | 42
     5 |    | 10 | 22 | 33 | 45
(5 rows)

create table foo2 (number int, subset int, value float8);
INSERT INTO foo2 VALUES(1,1,1.5*11),(1,2,1.4*22),(1,3,1.3*30),(1,4,1.2*43);
INSERT INTO foo2 VALUES(2,1,1.5*10),(2,2,1.4*23),(2,3,1.3*31),(2,4,1.2*46);
INSERT INTO foo2 VALUES(3,1,1.5*12),(3,2,1.4*24),(3,3,1.3*34),(3,4,1.2*47);
INSERT INTO foo2 VALUES(4,1,1.5*9),(4,2,1.4*23),(4,3,1.3*35),(4,4,1.2*42);
INSERT INTO foo2 VALUES(5,1,1.5*10),(5,2,1.4*22),(5,3,1.3*33),(5,4,1.2*45);
INSERT INTO foo2 VALUES(1,1,2.5*11),(1,2,2.4*22),(1,3,2.3*30),(1,4,2.2*43);
INSERT INTO foo2 VALUES(2,1,2.5*10),(2,2,2.4*23),(2,3,2.3*31),(2,4,2.2*46);
INSERT INTO foo2 VALUES(3,1,2.5*12),(3,2,2.4*24),(3,3,2.3*34),(3,4,2.2*47);
INSERT INTO foo2 VALUES(4,1,2.5*9),(4,2,2.4*23),(4,3,2.3*35),(4,4,2.2*42);
INSERT INTO foo2 VALUES(5,1,2.5*10),(5,2,2.4*22),(5,3,2.3*33),(5,4,2.2*45);

select number,
avg(case when subset = '0' then value else null end) as v0,
avg(case when subset = '1' then value else null end) as v1,
avg(case when subset = '2' then value else null end) as v2,
avg(case when subset = '3' then value else null end) as v3,
avg(case when subset = '4' then value else null end) as v4
from foo2
group by number order by number;

  number | v0 | v1 |  v2  |  v3  |  v4
--------+----+----+------+------+------
       1 |    | 22 | 41.8 |   54 | 73.1
       2 |    | 20 | 43.7 | 55.8 | 78.2
       3 |    | 24 | 45.6 | 61.2 | 79.9
       4 |    | 18 | 43.7 |   63 | 71.4
       5 |    | 20 | 41.8 | 59.4 | 76.5
(5 rows)

select * from crosstab(
   'select number, subset, avg(value) from
    foo2 group by number, subset order by number',
   'select * from (values(0),(1),(2),(3),(4)) as vc')
AS ct(code int, v0 float8, v1 float8, v2 float8, v3 float8, v4 float8);

  code | v0 | v1 |  v2  |  v3  |  v4
------+----+----+------+------+------
     1 |    | 22 | 41.8 |   54 | 73.1
     2 |    | 20 | 43.7 | 55.8 | 78.2
     3 |    | 24 | 45.6 | 61.2 | 79.9
     4 |    | 18 | 43.7 |   63 | 71.4
     5 |    | 20 | 41.8 | 59.4 | 76.5
(5 rows)

Joe