>Hi all
>
>I have the following tables:
>
>mItem
> iID, cCode
>
>tRequisition
> iID, iNo
>
>sRequisitionItems
> iID, iPID, iItemID, bQty
>
>I would like to have something like this for SQL Pass Through:
>

SELECT tR.iNo, tR.cBk, mI.cCode, sR.bQty
>   FROM tRequisitions tR
>      JOIN sRequisitionItems sR ON sR.iPID = tR.iID
>      JOIN mItems mI ON mI.iID = sR.iItemID
>   WHERE tR.dDt BETWEEN ?vp_dFromDt AND ?vp_dToDt
>   ORDER BY tR.iNo, tR.cBk, sR.iSrNo

>
>but just one line for each requisition, with columns for each mI.cCode with it's respective SUM(sR.bQty) like below:
>
>1, A, A4, 10, A3, 0, A0, 5, ...
>
>Hope I have been able to explain my requirements. I guess I can CASE WHEN, but that makes the statement long, if there is an efficient way, which can help when newer mItems.cCodes are added.
>
>Thanks.

Bhavbhuti,
In a single SQL you can do it like:

SELECT trI.No, ;
sum( iif( mI.Code = 'A', sR.bQty, 0 ) ) as 'A', ;
*... ;
group ...

This code could be converted to your backend's style. In SQL2005 there is PIVOT but even with that first you should do your summing (otherwise PIVOT is just a wrapper for easier coding exploding to a series of sum(iif())).
The code's inefficiency and pain of the effort to write it is obvious. Getting summed data into VFP and doing crosstabbing there (either with existing genxtab or your own xtabber) or doing that in a stored procedure/function of your backend would be better.
Cetin