I had a data set (pricing by state) delivered last week from a business application user that I needed to import into the application’s database. The user provided an Excel worksheet in the format below:
My database table, however, was designed as follows:
I needed a plan to transform the worksheet data to match my table’s structure.
First, I imported the data from the Excel workbook directly into a new table (
[tmpPrices]) using the SSMS Import and Export Wizard.
Then, I could
INSERT INTO SELECT from the imported table using the query below. The
UNPIVOT operator rotates the
[State] column identifiers into row values that align with a particular
[SKU]. The column specified before the
UNPIVOT operator (
[Price]) is the one that holds the values that are currently under the columns being rotated. The column that will contain the column values that I rotated follows the operator (
INSERT INTO [Prices]([Sku],[State],[Price])SELECT [SKU],[State],[Price]FROM(SELECT [SKU],[AR],[MS],[TN]FROM [tmpPrices]) p UNPIVOT([Price] FOR [State] IN([AR],[MS],[TN])) unpvt;