Convert Access CROSSTAB query to SQL Table or View

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

how to convert the access query above to sql server 2000

In SQL Server 2000 you have't have predefined operator to get the PIVOT table..

Here you have to manually write the query to get the pivot result...

(Example)

Code Snippet

Create Table #BikeSales
(
Year int,
Product Varchar(100),
Sales Int
)

Insert Into #BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into #BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into #BikeSales Values ('2007', 'HONDA F1', 7000)

Insert Into #BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into #BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into #BikeSales Values ('2007', 'HONDA IRL', 1000)

Insert Into #BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into #BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into #BikeSales Values ('2007', 'HONDA MotoGP', 132)

Insert Into #BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into #BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into #BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
[Main].Product
,Sum([2005].Sales) as [2005]
,Sum([2006].Sales) as [2006]
,Sum([2007].Sales) as [2007]
From (Select Distinct Product From #BikeSales) as [Main]
Left Outer Join (Select * From #BikeSales Where Year=2005) as [2005] On [2005].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2006) as [2006] On [2006].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2007) as [2007] On [2007].Product=[Main].Product
Group By [Main].Product

You can generate the above query dynamically using the following script..

Code Snippet

Declare @.JoinQuery as Varchar(1000);
Declare @.SelectQuery as Varchar(1000);
Declare @.PreparedJoinQuery as Varchar(1000);
Declare @.PreparedSelectQuery as Varchar(1000);
Select @.JoinQuery = '', @.SelectQuery = ''
Select @.PreparedJoinQuery = 'Left Outer Join (Select * From #BikeSales Where Year=?) as [?] On [?].Product=[Main].Product '
Select @.PreparedSelectQuery =',Sum([?].Sales) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From #BikeSales Group By Year

Exec ('Select [Main].Product' + @.SelectQuery + ' From (Select Distinct Product From #BikeSales) as [Main]' + @.JoinQuery + ' Group By [Main].Product')

|||

Using Manivannan's data, this method of creating a 'pivot' table in SQL 2005 is quite a bit more efficient. (Single Pass, No JOINS, NO Sub-Queries, No Dynamic SQL.)

Code Snippet

DECLARE @.BikeSales table
( [Year] int,
Product varchar(25),
Sales int
)

Insert Into @.BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into @.BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into @.BikeSales Values ('2007', 'HONDA F1', 7000)
Insert Into @.BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into @.BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into @.BikeSales Values ('2007', 'HONDA IRL', 1000)
Insert Into @.BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into @.BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into @.BikeSales Values ('2007', 'HONDA MotoGP', 132)
Insert Into @.BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into @.BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into @.BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
Product,
[2005] = sum( CASE [Year] WHEN 2005 THEN Sales END ),
[2006] = sum( CASE [Year] WHEN 2006 THEN Sales END ),
[2007] = sum( CASE [Year] WHEN 2007 THEN Sales END )
FROM @.BikeSales
GROUP BY Product
ORDER BY Product