A Crosstab unlike others... 1

[KCcasey]

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
[tt]43 -1 -1[/tt]
[tt]54 -1 -1[/tt]
[tt]56 -1[/tt]
[tt]76 -1[/tt]
***

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.

 

[iker3000]

Hi!

You have a big problem: there is nothing like crosstab query in MS-SQL. If you want to make something like this, you will have to build it dynamicaly (except you have a given number of column headers). This is not very easy, you have to build a Cursor on a select returning the different column headers, and the loop throug it while building the SQL string.

Iker

 

[tlbroadbent]

Does this help?

SELECT
t2.StockID AS ExtraStockID,
Min(Case When t1.ExtraHeader='4WD' Then -1 Else 0 End) As [4WD],
Min(Case When t1.ExtraHeader='AlloyWheels' Then -1 Else 0 End) As AlloyWheels,
Min(Case When t1.ExtraHeader='BullBars' Then -1 Else 0 End) As BullBars
FROM tblExtra t1
INNER JOIN tblStockExtra t2 ON t1.ExtraID = t2.ExtraID
INNER JOIN tblStock t3 ON t2.StockID = t3.StockID
WHERE t3.Sold=0
GROUP BY t2.StockID


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[KCcasey]

Thanks for your help tlb', that was right on the button! (sorry about the delay replying)

Tell me, I obviously need to better my skills with T-SQL -- do you know I any useful online resources?

Regards,
Casey.