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 27A1E3A6355 for ; Fri, 28 Jan 2005 16:34:36 +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 68760-04 for ; Fri, 28 Jan 2005 16:34:29 +0000 (GMT) Received: from joeconway.com (wsip-24-249-201-67.sd.sd.cox.net [24.249.201.67]) by svr1.postgresql.org (Postfix) with ESMTP id A8A9E3A6320 for ; Fri, 28 Jan 2005 16:34:28 +0000 (GMT) Received: from [206.19.64.3] (account jconway HELO [172.16.1.115]) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 2532404; Fri, 28 Jan 2005 09:35:44 -0800 Message-ID: <41FA6993.5000702@joeconway.com> Date: Fri, 28 Jan 2005 08:34:27 -0800 From: Joe Conway User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.7.3) Gecko/20040921 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Alexandre Leclerc Cc: Dawid Kuroczko , PERFORM Subject: Re: Flattening a kind of 'dynamic' table References: <1dc7f0e305012707234159cfc8@mail.gmail.com> <758d5e7f050127082755f9af12@mail.gmail.com> <1dc7f0e30501270943683ced12@mail.gmail.com> <758d5e7f050128000716f28a59@mail.gmail.com> <1dc7f0e305012806254e8540f4@mail.gmail.com> In-Reply-To: <1dc7f0e305012806254e8540f4@mail.gmail.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.048 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/582 X-Sequence-Number: 10272 Alexandre Leclerc 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 Sorry for jumping in on this thread so late -- I haven't been able to keep up with the lists lately. If I understand what you want correctly, you should be able to use crosstab from contrib/tablefunc: create table product_department_time(product_id int, department_id text, req_time int); insert into product_department_time values(906, 'A', 3000); insert into product_department_time values(906, 'C', 3000); insert into product_department_time values(906, 'D', 1935); insert into product_department_time values(907, 'A', 1500); insert into product_department_time values(907, 'C', 1500); insert into product_department_time values(907, 'D', 4575); insert into product_department_time values(924, 'A', 6000); insert into product_department_time values(924, 'C', 1575); select * from crosstab( 'select product_id, department_id, req_time from product_department_time order by 1', 'select ''A'' union all select ''C'' union all select ''D''' ) as (product_id int, a int, c int, d int); product_id | a | c | d ------------+------+------+------ 906 | 3000 | 3000 | 1935 907 | 1500 | 1500 | 4575 924 | 6000 | 1575 | (3 rows) You could make this dynamic for new values of department_id by wrapping it with a PL/pgSQL function. HTH, Joe