Stored Procedure to transform

Question

Stored Procedure to transform

James Asante

Old Hand

Points: 361
More actions
April 11, 2005 at 1:00 am

#61545

I have a crosstab query in MS Access 2000 which I want to duplicate as SP in sql 2000.
CREATE PROCEDURE QuickvoteCR
AS
TRANSFORM Count(Quickvote.ID) AS CountOfID
SELECT Quickvote.Comb, Count(Quickvote.ID) AS [Total Of ID]
FROM Quickvote
GROUP BY Quickvote.Comb
PIVOT Quickvote.Location;
I get the error message: Incorrect syntax near 'TRANSFORM'
Incorrect syntax near 'PIVOT'
Please help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

Firstly, what has this got to do with DTS?

Secondly, SQL 2000 doesn't support crosstab queries. You can either wait for SQL Server 2005 or search this site for crosstab, or pivot, and you'll find numerous examples to achieve what you need to do.

--------------------
Colt 45 - the original point and click interface

Don Becker Ten Centuries Points: 1185 More actions · Answer 2

James,

Can you explain why you need to do a cross tab in SQL?

If you are using reporting software, use the cross-tab in that.

Have you looked into reporting services?

GWLaws SSC Rookie Points: 45 More actions · Answer 3

There are times you want crosstab data on SQL Server, especially with Access ADP projects, but writing crosstab code on SQL Server 2000 can't really be done effectively.

If you know what your expected feilds are, you may be able to use DTS. I have 5 packages where I have monthly columns such as 200501, 200502, etc. Using a date field in the original row data I add a column for the month, going down. I have an Access MDB linked to this table from SQL Server. I create the crosstab in Access and a Make Table Query in Access to make a table of the crosstab. In the DTS I execuate that Make Table Query. Then, I import it back into SQL Server in DTS.

Since I know what my crosstab fields are, 200501, 200502, etc. I only have to do maintenance on my DTS transformations once a year. I also put some code to check for unexpeced dates. Works great ever night for the last 2 - 3 years.

Greg