Re: [SQL] 'denormalising' with a select

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