Geeks With Blogs


Google My Blog

Catch me at: The List!

My InstallScript Utility Belt My Amazon Wishlist
My Standard Disclaimer

Chris G. Williams Beware: I mix tech and personal interests here.
I need help (those of you who know me, know this already...)   :)
I've got about 20 Crosstab Queries in Access 2000 that need to be converted into Stored Procedures in SQL Server 2000. They are similar enough that if I can wrap my head around how to do one, I can figure out the rest.
This is the original Access 2000 query...
TRANSFORM Sum(TB.[CountOfBuyer Univ #]) AS [SumOfCountOfBuyer Univ #]

SELECT  TB.FirstOfBRepresentative, 
        TB.[FirstOfBuyer's Region], 
        TB.[FirstOfBuyer Area Code], 
        TB.[Buyer Univ #],
        First(TB.[Buyer Name]) AS [FirstOfBuyer Name],
        First(TB.[Buyer Contact]) AS [FirstOfBuyer Contact],
        First(TB.[Buyer Address]) AS [FirstOfBuyer Address],
        First(TB.[Buyer City]) AS [FirstOfBuyer City],
        First(TB.[Buyer State]) AS [FirstOfBuyer State],
        First(TB.[Buyer Zip]) AS [FirstOfBuyer Zip], 
        First(TB.[Buyer Telephone]) AS [FirstOfBuyer Telephone],
        First(TB.[Buyer Fax]) AS [FirstOfBuyer Fax],
        First(TB.[Buyer Email]) AS [FirstOfBuyer Email]
FROM [TBuyersMonthly #1] TB
WHERE (TB.FirstOfBTerritory=1)

GROUP BY TB.FirstOfBRepresentative, TB.[FirstOfBuyer's Region], 
         TB.FirstOfBTerritory, TB.[FirstOfBuyer Area Code], TB.[Buyer Univ #]

As you can see, I have multiple problems here:
1) T-SQL (2000) does not support the FIRST() function
2) T-SQL (2000) does not support the TRANSFORM and PIVOT commands. 
I feel like I have a decent grasp on T-SQL but I haven't had to do anything like this before. Any and all help would be greatly appreciated.
Posted on Monday, July 17, 2006 2:43 PM | Back to top

Comments on this post: technical question...

# re: technical question...
Requesting Gravatar...
Hmm... would this help?

Hope it helps! I tried to figure out how a Crosstab query works real quick to see if I could help (I have Stored Procedure experience, but I've never heard of a crosstab query until... just now when I read your article =) ), but I found it more confusing than Stored procedures. =)

If this works, I may have to pick your brain to figure out what that crosstab query does. It looks like it's performing a sum of statistics of some kind on a per month basis, but I've never seen "PIVOT" or "TRANSFORM".
Left by Alex Loret de Mola on Jul 17, 2006 11:14 PM

# re: technical question...
Requesting Gravatar...
Oh, silly me... the next page on that article I linked explains a lot more about Crosstab queries... and has a diagram showing how he converts crosstab to standard SQL Server compliant queries.

All I can say is "yikes", followed by "use that program in the link provided and it should do it all for you =)". Looks painful to do manually. I'm no fan of reinventing the wheel unless it looks like at least a few minutes of fun. =) =)
Left by Alex Loret de Mola on Jul 17, 2006 11:17 PM

Your comment:
 (will show your gravatar)

Copyright © Chris G. Williams | Powered by: