Hi Vlad
Thanks for your invaluable advise on SQL. I'm looking to engage some consulting help on database design and developing n-tier application with VPM and foxfire. Would u be available to help. If yes, please let me know your professional rates. The tools I'm using includes

1. xcase
2. xcase2vpm
3. vpm enterprise
4. foxfire enterprise
5. SQL Server/Oracle(maybe)

Best regards
>Ooops, sorry, it does not use sub-queries. It just uses the conditional fields.
>
>>Hi!
>>
>>Following is a query for the fixed number of the types:
>>
>>

>>Select p."Patient ID", p.Name,
>>    SUM(CASE v."Treatment Type" WHEN 1 THEN v.Amt ELSE 0 END) as Amt_1,
>>    SUM(CASE v."Treatment Type" WHEN 2 THEN v.Amt ELSE 0 END) as Amt_2,
>>    SUM(CASE v."Treatment Type" WHEN 3 THEN v.Amt ELSE 0 END) as Amt_3
>>  from Patient P LEFT JOIN Visit V
>>    ON P."Patient ID" = V."Patient ID"
>>  Group By p."Patient ID", p.Name
>>

>>
>>As you see, it uses Left-Joined subqueries. NVL is required to use 0 value instead of the NULL for case there are no amount for particular type.
>>
>>See help in MSDN, article index is "cross-tab reports (SQL Server)" for more explanations.
>>
>>If you have dynamic number of types, you can build query like above in a string and than run it:
>>- in VFP using SQLEXEC() function, where you pass the query string as a second parameter.
>>- in SQL Server stored procedure you can do a loop through the cursor of the treatment types and build a string, note that SQL Server 7.0 have a limitation to 8000 characters, so probably you will require to split a string or make a query written by as short way as possible. Than you can use 'EXEC ()' command to execute the query in a string, or use the sp_executesql stored procedure.
>>
>>HTH.
>>
>>>I have a patient table and a visits table.
>>>Patient
>>> Patient_ID
>>> Name
>>>
>>>Visit
>>> Patient ID
>>> Date
>>> Treatment Type
>>> Amt
>>>
>>>I wish to write a SQL statement that will generate a cursor as follows
>>>
>>> Patient_ID, Name, Amt_1, Amt_2, Amt_3
>>>
>>>where amt_1,amt_2,amt_3 are expenses incurred for treatment type 1, 2 & 3 respectively. The output is actually sort of crosstab. All help appreciated. Thanks.
>>>Best Regards