Flattening a kind of 'dynamic' table - Mailing list pgsql-performance

Good morning,

I have a table that links two tables and I need to flatten one.
(Please, if I'm just not in the good forum for this, tell me. This is
a performance issue for me, but you might consider this as an SQL
question. Feel free to direct me at the good mailling-list.)

design.products ---> design.product_department_time <--- design.departments

This allows us to fixe a given required time by department for a given product.
- Departments are defined by the user
- Products also
- Time is input for a department (0 and NULL are impossible).

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

I need to JOIN this data with the product listing we have to produce
and multiply the quantity with this time by departments, and all that
in a row. So departments entries become columns.

I did the following (I formated the query to help out):

SELECT
  product_id,
  sum(CASE WHEN department_id = 'A' THEN req_time END) AS a,
  sum(CASE WHEN department_id = 'C' THEN req_time END) AS c,
  sum(CASE WHEN department_id = 'D' THEN req_time END) AS d
FROM design.product_department_time
GROUP BY product_id;

 product_id |  a   |  c   |  d
------------+------+------+------
        924 | 6000 | 1575 |
        907 | 1500 | 1500 | 4575
        906 | 3000 | 3000 | 1935

Now in my software I know all the departments, so programatically I
build a query with a CASE for each department (just like the above).
This is nice, this is working, there is less than 10 departements for
now and about 250 jobs actives in the system. So PostgeSQL will not
die. (My example is more simple because this was an hard-coded test
case, but I would create a case entry for each department.)

But I am wondering what is the most efficient way to do what I need?

After that I need to link (LEFT JOIN) this data with the jobs in the
system. Each job has a product_id related to it, so USING (product_id)
and I multiply the time of each department with the quantity there is
to product. So someone can know how much work time there is to do by
departments.


Thanks for any input, comments, tips, help, positive criticism to
learn more, etc.

--
Alexandre Leclerc