DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 966,155 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Collin M. Barrett
Collin M. Barrett

Posted on • Originally published at collinmbarrett.com on

UNPIVOTing Data with T-SQL

Database Schema

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:

SKU TN MS AR
1 .99 .89 .89
2 1.09 .99 .99
3 .99 1.09 .99

My database table, however, was designed as follows:

Sku State Price
4 TN .89
4 MS .99
4 AR 1.09
5 TN .89
5 AR 1.09
5 MS .99

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

Oldest comments (0)

Update Your DEV Experience Level:

Settings

Go to your customization settings to nudge your home feed to show content more relevant to your developer experience level. πŸ›