It comes with SQL Server 2000 by default, but if you’ve gotten rid of it, or if you’re running Server 2005, you can download it from the Microsoft website. RequirementsĪll of my demonstration code will use the trusty Northwind sample database. I leave you with the advice that the script described here should be used carefully and sparingly, and not sprinkled willy-nilly about your databases. The stored procedure can have an adverse affect on performance if not used correctly, or used on an expensive or large data source. However, there are just as many, if not more, where it shouldn’t be used. I have found some applications for which this stored procedure was the ideal solution – I hinted at these in the first paragraph. It’s a balancing act that demands careful consideration. Just because it seems that you can’t do something, it doesn’t mean you shouldn’t. True, but I for one think that the opposite is also applicable. It has also been said that just because you can do something, it doesn’t mean you should. Anyone who has tried to pound data in SQL into a meaningful set of information, using a complicated set of business rules, will probably agree that SQL tends to discourage you from doing so, and the more fancy and creative you try to make your solution, the stronger that discouragement becomes. The SQL database engine’s primary role is the storage and retrieval of information, not the complex processing of it. There is a general rule which states that data manipulation of this sort is best left to the application or reporting levels of the system, and for good reason. It is for these exceptional cases that I decided to write a dynamic cross tab stored procedure. You’d like to export some data, already set out in the required format, to a text file.You are using a legacy application that you’d rather not fiddle with.You may be using a reporting solution that doesn’t provide this functionality.It won’t do to have the reporting system do it, nor is it feasible to build that functionality into the application.
Sometimes, you just absolutely have to generate a cross tab in SQL. Creating Cross Tab Queries and Pivot Tables in SQL - Simple Talk
0 kommentar(er)
