Re: Flattening a kind of 'dynamic' table

Alexandre wrote:
> Here a normal listing of design.product_department_time:
> product_id | department_id | req_time
> ------------+---------------+----------
> 906 | A | 3000
> 906 | C | 3000
> 906 | D | 1935
> 907 | A | 1500
> 907 | C | 1500
> 907 | D | 4575
> 924 | A | 6000
> 924 | C | 1575
> product_id | a | c | d
> ------------+------+------+------
> 924 | 6000 | 1575 |
> 907 | 1500 | 1500 | 4575
> 906 | 3000 | 3000 | 1935

ok, you have a couple of different options here. The first thing that
jumps out at me is to use arrays to cheat using arrays.
Let's start with the normalized result set.

select product_id, department_id, sum(req_time) group by product_id,
department_id

product_id | department_id | sum
924 a 6000
924 c 1575
907 a 1500
[...]

This should be no slower (in fact faster) then your original query and
does not have to be re-coded when you add new departments (you have a
department table, right?).

If you absolutely must have 1 record/product, you can cheat using
arrays:

select q.product_id,
array_accum(q.department_id) as depts,
array_accum(q.req_time) as times
from
(
select product_id, department_id, sum(req_time) as req_time
group by product_id, department_id
) q
group by q.product_id;

select product_id, array_accum(department_id) sum(req_time) group by
product_id

product_id | department_id | sum
924 {a, c} {1500, 1575}
[...]

disclaimer 1: I never checked syntax
disclaimer 2: you may have to add array_accum to pg (check docs)
Merlin