CASEWARE IDEA CROSS TABULATTION FULL
The Northwind database has a table called Categories, which is used to partition the full compliment of products into eight distinct groups, namely Beverages, Condiments, Confections, Dairy Products, Grains/Cereals, Meat/Poultry, Produce and Seafood.
![caseware idea cross tabulattion caseware idea cross tabulattion](https://i.ytimg.com/vi/SNFxXVyW9m0/maxresdefault.jpg)
Once Northwind has been downloaded and attached, create the sys_CrossTab stored procedure in the database and you’re on your way. 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.
CASEWARE IDEA CROSS TABULATTION CODE
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.
![caseware idea cross tabulattion caseware idea cross tabulattion](https://image.slidesharecdn.com/dataanalyticsforauditorsdataanalyticssoftwareslidesss-190416110308/95/data-analytics-for-auditors-data-analytics-software-19-638.jpg)
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.
![caseware idea cross tabulattion caseware idea cross tabulattion](https://slidetodoc.com/presentation_image/54caf7a0e548059f0fbf584c4645baac/image-6.jpg)
Sometimes, you just absolutely have to generate a cross tab in SQL. Creating Cross Tab Queries and Pivot Tables in SQL - Simple Talk