Subject Re: [firebird-support] Re: SQL 'DeNormalization' approaches
Author Michael Ludwig
Svein Erling schrieb am 12.10.2010 um 22:20 (-0000):

> > A simplistic overview of the challenge is:
> >
> > Input:
> > Name Table (One record per person)
> > Address Table (One to N records per person)
> > Phone Table (0 to N records per person)
> >
> > Output:
> > 1 record per person - with serialized columns for the various
> > address and phone values:
> >
> > ID FirstName LastName Add1 Add2 Add3 Phone1 Phone2 Phone2 <etc>

> WITH RECURSIVE AddressOrder(ID, Address, MyOrder) AS

Vim tells me there is a closing parenthesis missing from this
statement. :-)

> SELECT N.PersonID, N.FirstName, N.LastName, AO1.Address as Add1,
> AO2.Address as Add2, AO3.Address as Add3

If the number of addresses and phone numbers is not known, this approach
won't work. I think SQL won't work at all here. I'd say the requirement
is wrong.

You could concatenate all the address and phone records in a parseable
string representation, but it feels wrong as well.

Michael Ludwig