>Alejandro,
>
>I've done a lot of these. ** Air Code Warning! **
>
>
>SELECT Batch#, Trans#, ;
> SUM(IIF(Category = "RegTime", GrandChild.Quantity, 0)) AS RegTime_amt, ;
> SUM(IIF(Category = "Absence", GrandChild.Quantity, 0)) AS Absence_amt, ;
> ... ;
> FROM < be sure to include the parent tables with the proper joins > ;
> INTO CURSOR MyCursor ;
> GROUP BY Batch#, Trans#
>
>
This is one approach, but I don't see the virtue of a single SQL Select statement here; it's the perfect place for using SQL Select to filter a working subset of the data to (Parent INNER JOIN Child) INNER JOIN GrandChild, and generate a crosstab with end result (the Pivot Table Wizard in VFP6, or GENXTAB from earlier versions of FoxPro); it's especialy the case since you'd have to hard-code all the IIF()s in the Query to account for each category, and could easily end up running into the length of statement limitations. In addition, the IIF() isn't Rushmore-optimizabe, so you end up stepping through the tables IAC, doing the JOINs as Selects lets you create an intermediary result, and then crosstab the result of what can be optimized.
IOW SELECT the working set and pivot the result, rather than hard-coding categories and trying to stuff the whole thing into a single statement.
>I think this will give you the general idea.
>
>
>>Thanks for your reply, Eric.
>>
>>Problem is, after building the query as you suggest, the only way I know to fill table is with SCAN, which is very inefficient if there is a large amount of data.
>>
>>Is there a pure SQL way that anybody knows?
>>
>>Here is an example to make my question clearer.
>>
>>
>>Parent table (batches table):
>>Batch # Date and other info used to filter query
>>1 15/01/2000 etc.
>>2 31/01/2000
>>etc...
>>
>>Child table (transactions table)
>>Batch # Trans # Customer #
>>1 1 200
>>1 2 150
>>1 3 210
>>1 4 050
>>2 5 300
>>2 6 310
>>3 7 200
>>etc...
>>
>>Grandchild table (detail table) There are dozens of categories.
>>Trans # Category Name Quantity
>>1 RegTime 8.00
>>1 Factor1_25 2.00
>>1 Factor1_50 1.00
>>2 RegTime 2.00
>>3 RegTime 7.50
>>3 Absence 0.50
>>etc...
>>
>>Desired result:
>>Batch # Trans # RegTime Factor1_25 Factor1_50 Absence
>>1 1 8.00 2.00 1.00 0.00
>>1 2 2.00 0.00 0.00 0.00
>>1 3 7.50 0.00 0.00 0.50
>>etc...
>>
>>
>>Thanks.
>>
>>Alex
>>
>>>Build a select string by opening the table and adding to the select like:
>>>x = "select "
>>>scan
>>>x = x+" "+field+" AS "+field &&Blah blah blah...
>>>endscan
>>>
>>>Place a loop here to see if the table contains anything from the known list, anything not in the table add to the select string as a place holder.
>>>
>>>x = x+"0 AS Value, XX AS FieldName from table into table mytable"
>>>
>>>&x
>>>
>>>This should give you the names and corresponding values and zero for the others.
>>>
>>>HTH
>>
>>>Hi,
>>>
>>>Have parent/child/grandchild tables. Grandchild table may contain records that contain a category with its corresponding quantity. Whenever there is no grandchild record with a given category, it means its quantity is zero.
>>>
>>>We know aheard of time all possible categories.
>>>
>>>The question is: how do you efficiently create a table (via SQL SELECT's, ideally) that contains one record for each child member and that includes a column for each possible category with with the correct quantity in it.
>>>
>>>TIA,
>>>
>>>Alex
