Database developers who use Microsoft products have often experienced frustration at the disparity of functions between Access and SQL Server. SQL Server, which uses Transact-SQL (T-SQL), is a step behind Access in its range of features, although it is several steps ahead in terms of connectivity capabilities (i.e., more connections to a wider variety of clients). A favorite feature of Access and one that SQL Server users have been longing for is PIVOT tables. Their longing will end when SQL Server "Yukon" is released.
By the end of this article, you'll see why PIVOT functionality is something to look forward to.
What's a Microsoft PIVOT?
PIVOT is one of those cool, little features that make business intelligence (BI) developers look really good, and it's been a mainstay of Microsoft applications for most of the company's existence. It's also one of the reasons that so many more people now use Excel rather than Lotus 1-2-3. Bill Gates himself has often remarked on how much better his financial analysis became after he started using them.
In brief, a PIVOT operator turns columns into rows (denormalising the table), and an UNPIVOT operator turns the rows back into columns (renormalising the table).
Case scenario--vector graphics
Suppose you have a normalised database of SVG (Scalable Vector Graphics) images. There's one table for the image name and artist, and another table that gives the HTML OBJECT tag parameters for the DirectX SVG object.
Database tables
Table A holds the image name and artist and is coded like this:
CREATE TABLE SVGImages (
ImageID INT NOT NULL PRIMARY KEY,
ImageName NVARCHAR NOT NULL,
ArtistName VARCHAR NOT NULL);
The SVGImages table is populated with the data shown in Table A.
Table A
|
Table B holds the HTML OBJECT tag parameters for the various images:
CREATE TABLE SVGProperties (
ImageID INT NOT NULL REFERENCES SVGImages,
SVGProperty VARCHAR NOT NULL,
SVGPropertyValue SQL_VARIANT NOT NULL,
PRIMARY KEY (ImageID, SVGProperty);
The SVGProperties table is populated with the data shown in Table B.
Table B
|
The old way--sans PIVOT
Suppose you wanted to create a query that returned a table of images called GrowthLine and ShrinkageLine, but not the SalesLine images (1,2,3 and 6). Because of the normalization of the tables, in SQL Server 2000 the query would look something like Listing A.
The query in Listing A will return a derived table that looks like Table C.
Table C
|
You could also use an OLAP (OnLine Analytical Processing) data cube to get PIVOT functionality, but it is even more resource intensive if you don't need its full functionality.
The new way: PIVOT
The same results can be achieved using the PIVOT operator, but in a much more easily maintained form:
SELECT *
FROM SVGProperties AS MyDerivedTable
PIVOT (
MAX(SVGPropertyValue)
FOR SVGProperty IN([Line0001], [Line0002], [Line0003], [Line0004], [Line0005]
) AS MyPIVOT
WHERE ImageID in (1,2,3,6)
Other features
There are many features and considerations for the PIVOT operator that were not demonstrated here. Some of these include the UNPIVOT operator and the APPLY operators. You'll also want to make use of the power of PIVOT when used with JOINed tables. There are a variety of ways in which data can be PIVOTed into useful information.
Beyond the code: Why PIVOT?
Yukon is not yet released, so you may ask why you'd want to bother with the PIVOT operators as anything other than an academic exercise. The short answer is instant ROI. If you're currently building applications that get some of their value from their ability to produce reports, you can add the code for Yukon PIVOTs--but disable it when the back-end database is version 2000 or earlier. As soon as Yukon is released, you can enable the code and boast about how quickly you added new functionality.
Do you need help with MySQL? 
Leave a comment