From: Stuart Rison <stuart@ludwig.ucl.ac.uk>
To: pgsql-sql@postgresql.org
Cc: stuart@ludwig.ucl.ac.uk
Subject: [SQL] 'denormalising' with a select
Date: Fri, 28 May 1999 16:44:45 +0100
Message-ID: <v04020a00b37466607a13@[128.40.242.190]> (raw)
Hi there,
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 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).
Can a do 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.
PS. I'd love a cc: but I'll be fine without.
+-------------------------+--------------------------------------+
| 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 |
+-------------------------+--------------------------------------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: stuart@ludwig.ucl.ac.uk
Subject: Re: [SQL] 'denormalising' with a select
In-Reply-To: <v04020a00b37466607a13@[128.40.242.190]>
References: <v04020a00b37466607a13@[128.40.242.190]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox