Re: Fascinating SQL Problem

From: Chuck Hamilton <chuckh_at_ix.netcom.com>
Date: 1995/07/25
Message-ID: <3v2sov$ccq_at_ixnews5.ix.netcom.com>#1/1

In <3v0jp2$akh_at_news.ios.com> snowden_at_haven.ios.com (Roger Snowden) writes:
>
>RE: Interesting Sql Problem
>
>I am asked to provide a method of using SQL to retrieve rows in a denormalized
>manner. Here is the situation:
>
>I have two tables with a one-to-many relationship, the primary key is DonorID.
>The detail table Primary key is DonorID, ResponseDate.
>
>I want to retrieve data into a statistical package so that the view of the
 data
>is something like a spreadsheet row:
>
> From Parent Tbl Detail Row 1 Detail Row 2
> / / / / / /
>DonorID State ResponseDate1 Amount1 ResponseDate2 Amount2
> 99999 NY 01/01/95 10.00 05/01/95 5.00
>
>The ResponseDate, Amount columns are from successive detail rows. That is,
>Response1 and Amount1 are from the first detail of the given donor,
>Response2 and Amount2 are from the second detail
>Response3 and Amount3 are from the third detail, and so forth.
>
>How do you construct a view such that successive rows of detail (with the date
> value unknown) are presented as pseudo-columns in the retrieve. How do you
>report the most recent n rows as columns? Can this be done?
>
>Excel / Query allow it via a mechanism that Microsoft calls a Pivot. But,
>I believe that it is accomplished *after* the retrieve, by shoving row/columns
>into a spreadsheet cell. I need to get this data into a statistical tool
>using SQL alone.
>
>Any ideas? Is there something obvious I am overlooking?
>
>Thanks for any help.

I believe you're going to have to do this in multiple passes if you're going to use SQL. First you've got to run a query that determines the maximum number of detail rows associated with a single row in the master table. Only then can you determine what the final SQL statement will be, because you won't even know how many columns you need in the result until that's done. Then you'll have to include that many of instances of the detail table in the FROM clause of the query. For example if you determine that the most detail rows used by any single master row is 3, the query would need the detail table included 3 times. It would look like this...

SELECT donorid "DonorID",
state "State"

d1.responseDate "ResponseDate1",
d1.amount "Amount1",
d2.responseDate "ResponseDate2",
d2.amount "Amount2",
d3.responseDate "ResponseDate3",
d3.amount "Amount3"

FROM master, detail d1, detail d2, detail d3;

I'm not very familiar with the dynamic SQL features of PL/SQL in version 7, but this may be a job best done with that tool.

HTH

-- 
Chuck Hamilton
chuckh_at_ix.netcom.com

Incoming fire has the right of way!

Received on Tue Jul 25 1995 - 00:00:00 CEST