 |
Developers are frequently required to massage data from one form to another. This is especially common for database queries. I have frequently had to write queries that take normalized data and pivot it into a de-normalized table. For example, I have had to transform sales statistics stored with a separate record for each sales ID and quarter into a single record for each sales ID with separate columns for four quarters. The most efficient method to make this conversion in SQL Server is the use of a Pivot Table. MS Access provides a Cross Tab Query which automates this process. MS Excel has a Pivot Table Wizard that pivots data in a spreadsheet. However, until YUKON is released there is no automated method of performing this task in SQL Server.
To pivot data in SQL Server efficiently we have always utilized a Pivot table which is a table that transforms the form of the data. I have used a Pivot Table extensively in many systems to convert data from a normalized form to a de-normalized form. However, this week I have found a new use for a Pivot Table. I have found that the Pivot Table can be used to transform data in the opposite manner from a de-normalized form into a normalized form much more efficiently than any other method available. This article reviews the normalization of de-normalized data using a pivot table.
|
