Hi All

Please consider the following table and the data:

Table : RF
ItemCode    XnType     XnQty
AA          PUR         5
BB          PUR         6
CC          PUR         3
DD          PUR         6
AA          SLS         2
BB          SLS         1
CC          SLS         3
DD          SLS         1

Now I need to have the details of Purchase and Sales in Separate Columns Like This:

ItemCode    Purchase    Sale
AA          5           2
BB          6           1
CC          3           3
DD          6           1

Please tell me the SQL Server statement how to get the data in the above format. Althouth the same result can be get with the IIF function of Visual FoxPro. for example we can very much write the SQL statement in VFP to achive the avove result

VFP Stmt:

SELECT ItemCode, SUM(IIF(XnType='PUR', XnQty, 0) AS Purchase, ;
       SUM(IIF(XnType='SLS', XnQty, 0) AS Sale ;
       FROM rf GROUP BY ItemCode

what is the alternative of the above code in SQL Server 2000?

Please help me out.

Thanks


Raoshan