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
GROUP BY tblStock.StockID
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
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