Crosstab Convert

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 those query to either sql 2000 or 2005

please help me.....!!

I am really wondering about this Question...

I am seeing the same question for 3rd time in this forum...

Any how if you use SQL Server 2000, you have to use the following dynamic query...

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 OILPALM as [?] On [?].Year=? and [?].NAME=[Main].NAME '
Select @.PreparedSelectQuery =',Count([?].BLOCK) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From OILPALM Group By Year

Exec ('Select [Main].NAME' + @.SelectQuery + ' From (Select Distinct NAME From OILPALM) as [Main]' + @.JoinQuery + ' Group By [Main].NAME')

If you use SQL Server 2005,

Code Snippet

select * from oilpalm PIVOT( SUM(Block) for Year In ([2006], [2005])) as Pvt

Or --Dynamic Values will fetch all the data need not to hardcode the year values

Declare @.PreparedStatement as varchar(1000);
Declare @.Query as varchar(1000);
Select @.PreparedStatement = ',[?]', @.Query ='';
Select @.Query = @.Query + Replace(@.PreparedStatement,'?',Year) From oilpalm Group By Year;
Select @.Query = Substring(@.Query,2,Len(@.Query))

Exec ('Select * from oilpalm PIVOT( SUM(Block) for Year In (' + @.Query + ')) as Pvt')

|||is there any code which is more simple then you posted|||

For SQL Server 2000.

Code Snippet

Select
[Main].NAME,
Count([2005].BLOCK) as [2005],
Count([2006].BLOCK) as [2006]
From
(Select Distinct NAME From OILPALM) as [Main]
Left Outer Join OILPALM as [2005] On [2005].Year=2005 and [2005].NAME=[Main].NAME
Left Outer Join OILPALM as [2006] On [2006].Year=2006 and [2006].NAME=[Main].NAME
Group By
[Main].NAME

For SQL Server 2005

Code Snippet

select

*

from

oilpalm

PIVOT( SUM(Block) for Year In ([2006], [2005])) as Pvt

|||

I suggest that you take the time to work through the suggestions that have been provided to your previous posts until you understand the process.

When you work through the process and begin to understand the process, you will be able to work these out for yourself. (Otherwise, we might think that you are just trying to get folks to do your work for you. And that probably wouldn't be fair to you ...)