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