> Hi all,
> Though not solely a FB thing, I always appreciate the quality
> responses I get from this list (and I remain a loyal, and quiet,
> FB aficionado !)
>
> I'm looking for a good reference to get me started crafting up a
> set of canned SQL select statements which will help 'convert' a
> multi'table normalized view of data, into the more simplistic 'One
> Record Per Individual' approach that many of my research users
> prefer. These users who request this are primarily SAS users.
> (SAS SQL exists, but not pertinent to this discussion !)
>
> 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>
>
> Thanks for any experience you can provide !
> Rich Pinder
> USC School of Medicine

Hi Rich!

I've done similar things in Firebird - even to the extent of reaching Firebirds limits (when the result set contains a few thousand fields, you cannot have too long field names). Generally, our researchers use Stata or SPSS and quite frankly, I think these programs are better at 'flattening' tables than Firebird. Though it can be done in Firebird if the resulting dataset doesn't get too wide. I'd recommend something like (just flattening address, expand yourself to add phone). I typically do it this way:

WITH RECURSIVE AddressOrder(ID, Address, MyOrder) AS
(SELECT AT1.PersonID, AT1.ADDRESS, CAST(1 as Integer)
FROM AddressTable AT1
WHERE NOT EXISTS (SELECT * FROM AddressTable AT2
WHERE AT1.PersonID = AT2.PersonID
AND AT1.Address > AT2.Address)
UNION ALL
SELECT AT3.PersonID, AT3.ADDRESS, AO.MyOrder+1
FROM AddressOrder AO
JOIN AddressTable AT3 ON AO.ID = AT3.PersonID
WHERE NOT EXISTS (SELECT * FROM AddressTable AT4
WHERE AT3.PersonID = AT4.PersonID
AND AT3.Address > AT4.Address
AND AO.Address < AT4.Address)
SELECT N.PersonID, N.FirstName, N.LastName, AO1.Address as Add1,
AO2.Address as Add2, AO3.Address as Add3
FROM NAME N
JOIN AddressOrder AO1
ON N.PersonID = AO1.Id
LEFT JOIN AddressOrder AO2
ON N.PersonID = AO2.Id
AND AO2.MyOrder = 2
LEFT JOIN AddressOrder AO3
ON N.PersonID = AO3.Id
AND AO2.MyOrder = 3
WHERE AO1.MyOrder = 1

though I think EXECUTE BLOCK may be more appropriate for this particular type of request (then you don't need the WITH RECURSIVE, I just have never used EXECUTE BLOCK myself and it is too late for me to try something new today).

If you get plenty of these request, another option is of course to write a program that can create the required SELECT statement depending on user input (and possibly reading the system tables).

HTH,
Set