Обсуждение: [SQL] 'denormalising' with a select

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              |
+-------------------------+--------------------------------------+

At 18:44 +0300 on 28/05/1999, Stuart Rison wrote:


> 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 |     |     |     |

The question is, why would you want to do something like that? It doesn't
add any more information than your basic table has. It is merely a
different way of presenting the same information.

Do you simply want to display it this way? What do you want to do with it
on the frontend?

It's not impossible. You can do this with a five-way self-join and a lot of
<> clauses in the where. It would be terribly inneficient. If all you want
is to display it like this, you should simply have your frontend read the
table ordered by brecard_id, and print the code field horizontally until it
encounters a different brecard_id. Another option is to have an aggregate
that concatenates the codes, and have your frontend decompose the resulting
string. I think the first method is easier in this particular scenario.

I don't think there is a way you can do the above in SQL without knowing
the maximum number of codes in advance.

Herouth


--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma