Received: from corppo1.uvsg.com (corppo1.uvsg.com [192.40.65.90]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA91790 for ; Tue, 1 Jun 1999 11:32:40 -0400 (EDT) (envelope-from michael.j.davis@tvguide.com) Received: from prevuepo1.uvsg.com ([192.40.65.44]) by corppo1.uvsg.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2448.0) id L4BCRSP2; Tue, 1 Jun 1999 10:32:40 -0500 Received: by prevuepo1.uvsg.com with Internet Mail Service (5.5.2448.0) id ; Tue, 1 Jun 1999 10:35:37 -0500 Message-ID: <93C04F1F5173D211A27900105AA8FCFC145550@lambic.prevuenet.com> From: Michael J Davis To: "'Stuart Rison'" , pgsql-general@postgreSQL.org Subject: RE: [GENERAL][SQL] 'denormalising' with a select Date: Tue, 1 Jun 1999 10:33:42 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain Here is a version using aggregation: create function get_codes_agf(text, text) returns text as ' begin if (length($1) > 0) then return $1 || '', '' || $2 else return $2; end if; end' language 'plpsql'; CREATE AGGREGATE get_codes_ag ( sfunc1 = get_codes_agf, basetype = text, stype1 = text, initcond1 = '' ); select brecard_id, get_codes_ag(code) from table_name group by brecard_id; > > -----Original Message----- > > From: Stuart Rison [SMTP:stuart@ludwig.ucl.ac.uk] > > Sent: Tuesday, June 01, 1999 6:54 AM > > To: pgsql-general@postgreSQL.org > > Subject: [GENERAL][SQL] 'denormalising' with a select > > > > Hi there, > > > > This was posted to SQL where it 'truly' belongs but I got no answwer and > > since it has a bit of database design in it (and a lot more people seem > to > > read [GENERAL]) I thought I'd try it here. > > > > Consider a table like this: > > > > brecard_id |code > > ----------------+---- > > IEGA18051999006 |COME > > IPHA04031999004 |CRIB > > IPHA04031999005 |COME > > IPHA04031999005 |CRIB > > IPHA26021999006 |SOLI > > IPHA26021999010 |COME > > IPHA26021999010 |SOLI > > ISTL04031999001 |CRIB > > IUCH03031999003 |COME > > IUCH03031999003 |CRIB > > IUCH03031999003 |MICR > > IUCH03031999003 |SOLI > > > > each combination of id and code is unique (they form a composite primary > > key) > > but any brecard_id could have 1 or more codes associated with it > > (theoretically with no upper boundary but let us say a maximum of 5 > > codes). > > > > Is there a SELECT which will turn each of the codes for one brecard_id > > into > > a column... ie. > > > > brecard_id |code1|code2|code3|code4|code5 > > ----------------+-----+-----+-----+-----+----- > > IEGA18051999006 |COME | | | | > > IPHA04031999004 |CRIB | | | | > > IPHA04031999005 |COME |CRIB | | | > > IPHA26021999006 |SOLI | | | | > > IPHA26021999010 |COME |SOLI | | | > > ISTL04031999001 |CRIB | | | | > > IUCH03031999003 |COME |CRIB |MICR | | > > IUCH03031999003 |SOLI | | | | > > > > and here a a few more brainteasers for you gurus out there... > > > > 1) I'm actually not fussed about the order the codes appear in the > > columns, > > but let's say the order mattered, would this affect the SELECT(s)? > > 2) Would it make the query easier if I knew the maximum number of codes > > one > > brecard_id could have? > > 3) (this one for true Wizards -and Sorceresses, Herouth ;)- only) Could > > you > > write a 'generalised' query which could cope with tables having variable > > 'maximum' numbers of codes associated with each brecard_id? > > > > For the life of me I don't know how you'd do it in SQL, I have PL/pgSQL > > and > > have started playing around with that but I'd hate to re-invent the > wheel! > > > > regards, > > > > Stuart. > > > > +-------------------------+--------------------------------------+ > > | Stuart Rison | Ludwig Institute for Cancer Research | > > +-------------------------+ 91 Riding House Street | > > | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | > > | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | > > +-------------------------+--------------------------------------+