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