SELECT ItemCode, SUM(CASE WHEN XnType='PUR' THEN  XnQty ELSE 0 END) AS Purchase,
                 SUM(CASE WHEN XnType='SLS' THEN  XnQty ELSE 0 END) AS Sale
FROM rf
GROUP BY ItemCode

>
>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