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;