Please Help me Conversion CrossTab Access to SQL Server 2000 Query

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]);

Please Help me

Thank You

Hi Sam,

Today, my recommendation is the same one that I gave you yesterday. Click here to check it out. Several folks have taken their time and given you suggestions -and you don't seem to be learning from those suggestions.

In fact, I'll post it again:

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 ...)

|||not understand the process Function TRANSFORM and PIVOT?please help me

thank you

|||

Transform is NOT a Transact-SQL keyword -in this situation, you can probably safely ignore it.

PIVOT is a SQL 2005 keyword that displays rows as columns.

You 'should' have a copy of SQL Server 2005 Books Online. You can get a lot of good information from that source.

A couple of folks indicated to you in your other posts that IIF was not a SQL function (it is an Access function), and they offered suggestions about how to turn IIF functions into CASE structures. Perhaps looking in Books Online and reading about using CASE would be useful.

I, for one, am willing to assist those that seem intent on learning and helping themselves. BUT I get annoyed to repeated be asked to do someone's work for them -UNLESS, of course, I'm getting paid for it.

Just in case others here are not so compelled as I, I shall refrain from additional comments on your posts. Good luck.

|||

This query will help you...

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')

|||This query help me....Thank you so much...