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#

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