Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 46F899FBEAD for ; Fri, 12 Oct 2007 12:44:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 20880-04-6 for ; Fri, 12 Oct 2007 12:43:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from ns1.officenet.no (vpn.officenet.no [193.212.174.13]) by postgresql.org (Postfix) with ESMTP id 149399FBF14 for ; Fri, 12 Oct 2007 12:43:56 -0300 (ADT) Received: from [193.212.174.32] by ns1.officenet.no with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.63) (envelope-from ) id 1IgMg0-00014X-1O; Fri, 12 Oct 2007 17:43:54 +0200 From: Andreas Joseph Krogh Organization: OfficeNet AS To: pgsql-sql@postgresql.org Subject: Re: OT: array_accum equivalent in Oracle Date: Fri, 12 Oct 2007 17:43:39 +0200 User-Agent: KMail/1.9.6 Cc: "Jonah H. Harris" References: <200710121616.28023.andreak@officenet.no> <36e682920710120802u4c8fa4c9xc7021f59c5579ba@mail.gmail.com> In-Reply-To: <36e682920710120802u4c8fa4c9xc7021f59c5579ba@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200710121743.39228.andreak@officenet.no> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/63 X-Sequence-Number: 29435 On Friday 12 October 2007 17:02:23 Jonah H. Harris wrote: > On 10/12/07, Andreas Joseph Krogh wrote: > > Anybody knows if Oracle has an equivalent of PG's array_accum or > > ARRAY(subselect) construct? > > Something like this: > > CREATE TYPE varchar2_table_t AS TABLE OF VARCHAR2(32767); > SELECT job, CAST(MULTISET(SELECT ename FROM emp WHERE job =3D e.job) AS > varchar2_table_t) FROM emp e GROUP BY job; Amazing! Works like a charm. I was envisioning some pretty ugly PL/SQL functions to accomplish this. Thanks! =2D-=20 Andreas Joseph Krogh Senior Software Developer / Manager =2D-----------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst All=E9 11 | know how to do a thing and to watch | PO. Box 529 Sk=F8yen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Tlf: +47 24 15 38 90 | | =46ax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | =2D-----------------------+---------------------------------------------+