Fascinating SQL Problem

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.

Roger     Received on Mon Jul 24 1995 - 00:00:00 CEST