Re: Column as arrays.. more efficient than columns?

Ow Mun Heng wrote:
> => select code, subset, avg(value)  from foo  group by subset, code;
>  code | subset |         avg
> ------+--------+---------------------
>  A    | 3      | 98.0000000000000000
>  A    | 1      | 20.0000000000000000
>  A    | 4      | 98.0000000000000000
>  A    | 0      | 98.0000000000000000
>  A    | 2      | 98.0000000000000000
>

An alternative way to get the output below, would be to feed your
aggregate query above to the crosstab() function in contrib/tablefunc.

Joe

> => select code, 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 code;
>  code | v0 | v1 | v2 | v3 | v4
> ------+----+----+----+----+----
>  A    | 98 | 20 | 98 | 98 | 98