I've shamelessly borrowed from it to explain this first shot at a Cross Tab.
The Cross Tab Report example from Books Online is very simple and easy to understand. but I don't have access to either which means I haven't tested it.Īlso, for your convenience, all of the code has been attached in the "Resources" section near the end of the article. I'm pretty sure that most of this will work on 2008 and that a good portion of the code for Cross Tabs will also work on 7. I indicate which rev each section of code will run on in parenthesis. Last but not least, I currently only have SQL Server 20 installed. Also, some of the data we'll end up using is a wee bit bigger than what I would normally use a table variable for. Sure, I could have used Table Variables, but they don't really allow for people to do partial runs and they don't all people to look and see what's in the Table Variable after each section. I wrote all of the example code and data using Temp Tables just to be safe. But, like I said about the number of recent number of posts, folks get forced into a corner by their bosses and, if they have to do such a thing, I thought they could use a little help. I'm also aware that using EAV/NVP tables isn't considered to be a "best practice". there was a while when not a day went by when two or three such requests were posted each day.Īlso, yes, I aware that a lot of this type of "formatting" should be done in the GUI, reporting tool, or maybe even a Spreadsheet. The reason I'm writing a series of articles on the simple concept of Cross Tabs and Pivots is because of the recent number of requests for this type of information on the SQL Server Central forums. The purpose of this article is to provide an introduction to Cross Tabs and Pivots and how they can be used to "rotate" data. In other words, you can use a Cross Tab or Pivot to convert or transpose information from rows to columns either for reporting or to convert some special long skinny tables known as EAV's or NVP's into a more typical form data. This is known as creating a PivotTable®, creating a cross-tab report, or rotating data." "Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. I looked for a definition of what a "Cross Tab" actually is and, after a slight modification, couldn't find a better one than what's in SQL Server 2000 Books Online. We've fought and won many battles together.)
(This article is dedicated to a good friend and fellow T-SQL warrior, Katrina Wright.
0 kommentar(er)
