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
Vlad Grynchyshyn, Project Manager, MCP
vgryn@yahoo.comICQ #10709245
The professional level of programmer could be determined by level of stupidity of his/her bugs
It is not appropriate to say that question is "foolish". There could be only foolish answers. Everybody passed period of time when knows nothing about something.