Crosstab Query

I have a query running on MS Access like this :
TRANSFORM Count(ActivityClass) AS CountOfActivityClass
SELECT ActivityClass
FROM Category
GROUP BY ActivityClass
PIVOT Type
but how it's can be run on SQL Server, is there any function on SQL to run
the Transform function?
ThanksHi
There is no function in SQL 2000 that will do this, the most efficient way
is to do it within the application. But you may also want to check out:
http://support.microsoft.com/defaul...b;EN-US;q175574
or
http://www.sqlteam.com/item.asp?ItemID=2955
or
http://www.rac4sql.net/
John
"Firmansyah" wrote:

> I have a query running on MS Access like this :
> TRANSFORM Count(ActivityClass) AS CountOfActivityClass
> SELECT ActivityClass
> FROM Category
> GROUP BY ActivityClass
> PIVOT Type
> but how it's can be run on SQL Server, is there any function on SQL to run
> the Transform function?
> Thanks
>
>|||Hi,
Thanks very much for the infomation
Firman
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:0195E062-2769-4202-B065-FD180D2958FF@.microsoft.com...
> Hi
> There is no function in SQL 2000 that will do this, the most efficient way
> is to do it within the application. But you may also want to check out:
> http://support.microsoft.com/defaul...b;EN-US;q175574
> or
> http://www.sqlteam.com/item.asp?ItemID=2955
> or
> http://www.rac4sql.net/
> John
>
> "Firmansyah" wrote:
>

Crosstab in SQL 2000./.net 2003

Hi al

Don't know this is the right forum, but here is my question anyway

Is there a way to make a crosstab like you can in access with the transform statement

Regards

Remco

Not easily you have to build some dynamic SQL, if you do a search on crosstab and SQL server you should come across a number of scripts|||

SimonSa wrote:

Not easily you have to build some dynamic SQL

that is just crazy

remco

|||Thats why its been added into SQL 2005|||

Check out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=249991&SiteID=1

HTH

|||

Ok tnx (sight)

Remc

|||

Please update post to answered if that answered your question

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