Nadya,

1. The IIF() VFP function can be easily replaced by SQL CASE function.
2.

SELECT MainInfo.*, ;
  ...
  group by MainInfo.CredID

wouldn't work neither in VFP8 nor in SQL Server because column list includes columns that or not agregate functions and are not in GROUP BY list. You can either add all columns from the list to the GROUP BY list or get just key fields and than join result as derived table back to the source table.
3. NamesInfo.LastName like ?NameInput If you're using SPT (SQLEXEC) than it'll still work. If this select is a part of sproc, than you'll use T-SQL variable instead of SPT parameter NamesInfo.LastName like @NameInput

SELECT MainInfo.CredId, ;
    sum(CASE WHEN MainInfo.Category = "1" THEN 1 ELSE 0 END) as Category1Count, 
    sum(CASE WHEN MainInfo.Category = "2" THEN 1 ELSE 0 END) as Category2Count 
from MainInfo 
  inner join NamesInfo 
    on MainInfo.CrediID = NamesInfo.CrediID 
  where MainInfo.ccode = "01" 
  and MainInfo.town in (select Town from SelectedTowns) 
  and NamesInfo.LastName like ?NameInput 
  group by MainInfo.CredID

>Hi everybody,
>
>How this VFP SQL will look in SQL Server:
>

>SELECT MainInfo.*, ;
>sum(iif(MainInfo.Category = "1",1,0)) as Category1Count, ;
>sum(iif(MainInfo.Category = "2",1,0)) as Category2Count ;
>from MainInfo inner join NamesInfo ;
>on MainInfo.CrediID = NamesInfo.CrediID ;
>where MainInfo.ccode = "01" and ;
>MainInfo.town in (select Town from SelectedTowns) ;
>and NamesInfo.LastName like ?NameInput ;
>group by MainInfo.CredID into cursor curResult

>
>Thanks a lot in advance.