First of all, a disclaimer:

The report you asked for is something that is likely best handled in the front end. This really is not a cross tab report (at least not the way I define it.) This is combining 2 separate data sets into one. The data you are trying to display in the same row has no true relation other than the fact that they're transactions on the same product ordered by their date.

That said, you can get the output you're requesting like this:

[font="Courier New"]IF OBJECT_ID('TempDB..#tblInv','U') IS NOT NULL

DROP TABLE #tblInv

CREATE TABLE #tblInv

(

ID         INT,

PartNo         VARCHAR(10) NOT NULL,

TranType   VARCHAR(7) NOT NULL,

Quantity   INT NOT NULL,

PRIMARY KEY CLUSTERED(PartNo, TranType, ID),

RN     INT,

BSOrder        INT

)

INSERT INTO #tblInv(id,partno, TranType,quantity)

SELECT 1, 'A', 'buy',  50 UNION ALL

SELECT 2,'A', 'buy',  50 UNION ALL

SELECT 3,'A', 'buy',  50 UNION ALL

SELECT 4,'A', 'buy',  50 UNION ALL

SELECT 5,'A', 'buy',  100 UNION ALL

SELECT 6,'A', 'sell',  150 UNION ALL

SELECT 7,'A', 'sell',  100 UNION ALL

SELECT 8,'A', 'buy',  500 UNION ALL

SELECT 9,'A', 'sell',  450 UNION ALL

SELECT 10,'A' ,'buy',  450 UNION ALL

SELECT 11,'A', 'sell',  550 UNION ALL

SELECT 12,'B', 'buy',  300 UNION ALL

SELECT 13,'B', 'sell',  200 UNION ALL

SELECT 14,'B' ,'buy',  200 UNION ALL

SELECT 15,'B', 'sell',  250 UNION ALL

SELECT 16, 'B', 'sell',  250

DECLARE @RN         INT,

   @BSOrder    INT,

   @ID         INT,

   @LastPart   VARCHAR(10),

   @LastType   VARCHAR(7)

SET @RN = 0

SET @BSOrder = 1

UPDATE #tblInv

SET    @RN = RN = @RN + 1,

   @BSOrder = BSOrder = CASE WHEN @LastPart = PartNo AND @LastType = TranType THEN @BSOrder + 1 ELSE 1 END,

   @LastPart = PartNo,

   @LastType = TranType,

   @ID = [ID] -- Anchor

FROM #tblInv WITH (INDEX(0))

SELECT     COALESCE(B.RN, S.RN) RN,

   ISNULL(B.[ID],0) BID,

   COALESCE(B.PartNo,S.PartNo) PartNo,

   ISNULL(B.TranType,'-') TranType,

   ISNULL(B.Quantity,0) Quantity,

   ISNULL(S.[ID],0) SID,

   ISNULL(S.Quantity,0) sQuantity

FROM   (SELECT * FROM #tblInv WHERE TranType = 'buy') B

   FULL OUTER JOIN

   (SELECT * FROM #tblInv WHERE TranType = 'sell') S

   ON B.PartNo = S.PartNo AND B.BSOrder = S.BSOrder

ORDER BY COALESCE(B.RN, S.RN)[/font]