>I'm having to generate a report cursor with multiple child records displayed like part of the parent record. I thought there was an easy SELECT based trick to do this, but I'm not having much luck except by brute force. Can anyone help improve this?
>
>

>SELECT Parent.TranID, Parent.Amount, Child.Tax_Amount as Tax1, 0 as Tax2, 0 as Tax3;
> FROM Parent LEFT OUTER JOIN Child on Parent.TranID = Child.TranID AND Child.Tax_Number = 1 INTO CURSOR t1
>SELECT Parent.TranID, Parent.Amount, 0 as Tax1, Child.Tax_Amount as Tax2, 0 as Tax3;
> FROM Parent LEFT OUTER JOIN Child on Parent.TranID = Child.TranID AND Child.Tax_Number = 1 INTO CURSOR t2
>SELECT Parent.TranID, Parent.Amount, 0 as Tax1, 0 as Tax2, Child.Tax_Amount as Tax3;
> FROM Parent LEFT OUTER JOIN Child on Parent.TranID = Child.TranID AND Child.Tax_Number = 1 INTO CURSOR t3
>
>SELECT TranID, Amount, t1.Tax1, t2.Tax2, t3.Tax3 FROM t1 LEFT OUTER JOIN t2 ON t1.TranID = t2.TranID and t1.Amount = t2.Amount LEFT OUTER JOIN t3 ON t1.TranID = t3.TranID and t1.Amount = t3.Amount 
>

>
>Chris.

SELECT Parent.TranID, Parent.Amount, 
MAX(IIF(Child.Tax_Number = 1, Child.Tax_Amount, 00000000.00)) AS Tax1,
MAX(IIF(Child.Tax_Number = 2, Child.Tax_Amount, 00000000.00)) AS Tax2,
MAX(IIF(Child.Tax_Number = 3, Child.Tax_Amount, 00000000.00)) AS Tax3
FROM Parent LEFT JOIN Child ON Parent.TranID = Child.TranID
GROUP BY Parent.TranID, Parent.Amount

Take a look at this blog post (even though it applies to SQL Server, the concept is the same in VFP):

http://blogs.lessthandot.com/index.php/datamgmt/datadesign/understanding-sql-server-2000-pivot