X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 44B553A5C41 for ; Thu, 27 Jan 2005 15:23:42 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 73793-02 for ; Thu, 27 Jan 2005 15:23:34 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by svr1.postgresql.org (Postfix) with ESMTP id DFCA63A5F60 for ; Thu, 27 Jan 2005 15:23:37 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 40so264889wri for ; Thu, 27 Jan 2005 07:23:34 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=cOM6eISvCQeup4mxzgdrhLwcdnfEM8Gf9Bq9OcnvLBtIQb4yewBywoH2IjhtGaAC6aWLG4Znhj+rISzZy/lewQwS333cHs10S0HE78gD329jSOWUOuhU3wEfbgonPHMNIyYGZug8BXv88Q8E7NTA73aYFixmOFaGpbUTITnLu5w= Received: by 10.54.20.36 with SMTP id 36mr93918wrt; Thu, 27 Jan 2005 07:23:34 -0800 (PST) Received: by 10.54.20.47 with HTTP; Thu, 27 Jan 2005 07:23:34 -0800 (PST) Message-ID: <1dc7f0e305012707234159cfc8@mail.gmail.com> Date: Thu, 27 Jan 2005 10:23:34 -0500 From: Alexandre Leclerc Reply-To: Alexandre Leclerc To: PERFORM Subject: Flattening a kind of 'dynamic' table Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/539 X-Sequence-Number: 10229 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