Actually you don't need a cross tab/pivot....

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

)

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

select * from #tblInv

SELECTCOALESCE(b.id,s.id) AS id, COALESCE(b.id,0) AS bid, COALESCE(b.partno,s.partno) AS partno,

COALESCE(b.TranType,s.TranType) AS TranType, COALESCE(b.quantity,0) AS bquantity,

COALESCE(s.id,0) AS [sid], COALESCE(s.quantity,0) AS squantity

FROM(

SELECTROW_NUMBER() OVER( PARTITION BY partno ORDER BY id ) AS rowid, *

FROM#tblInv

WHERETranType = 'buy'

) b

FULL OUTER JOIN

(

SELECTROW_NUMBER() OVER( PARTITION BY partno ORDER BY id ) AS rowid, *

FROM#tblInv

WHERETranType = 'sell'

) s ON b.partno = s.partno AND b.rowid = s.rowid

Edit:

Aaaah, I think I had too much coffee in the day:w00t:, posting 2K5 solutions in 2K forums....

Just wait for few minutes, I'll be back with 2K solution...