UNPIVOTing Data with T-SQL

Requirement

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.

A Solution

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 ([State]).

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;

via Microsoft