Hi,

I'm in the position where I am migrating a Access 2002 DB to SQL Serv' 7.

I'm seeking some help, specifically how to write a SP to emulate the results that an Access crosstab query generates.

I've done a some research, and read several articles on rewriting aggregate type crosstabs, but these haven't helped (or I haven't understood what they were pointing out!)

I'm new to the game and would appreciate any help

Here's the Crosstab query...
***
TRANSFORM First(IIf([ExtraAbbrv] Is Not Null,True,False)) AS test
SELECT tblStock.StockID AS ExtraStockID
FROM (tblExtra INNER JOIN tblStockExtra ON tblExtra.ExtraID = tblStockExtra.ExtraID) INNER JOIN tblStock ON tblStockExtra.StockID = tblStock.StockID
WHERE (((tblStock.Sold)=0))
GROUP BY tblStock.StockID
PIVOT tblExtra.ExtraHeader;
***

The result set generated lists each stock item as a row, a (-1) value for a row - column intersection indicates a stock item 'has' the associated extra.

Here's a typical result set (in Access)...
***
ExtraStockID 4wd alloyWheels Bullbars
43 -1 -1
54 -1 -1
56 -1
76 -1
***

Table structure for tblExtra...
***
ExtraID ExtraDesc ExtraAbbrv ExtraHeader Group
01 4 wheel 4WD 4wd 14
07 Alloy wheels AW alloyWheels 12
09 Bull Bars BB BullBars 23
***

Table structure for tblStockExtra...
***
ExtraStockID StockID ExtraID
01 22 01
02 45 04
03 45 05
04 67 45
***

Thanks in advance!

Casey.