Unpivot Your World

Unpivoting your data means grabbing a data series from buckets across the columns of your data source (usually a spreadsheet) and transposing along the rows instead.   This can be accomplished with a wide variety of tools, however two will be covered here, T-SQL and the Data Explorer plugin for Excel 2013.

The TQL Example is provided below using a sample dataset that has the Year pivoted in 3 columns, 1 column for each year in the dataset, 2004, 2008 and 2012.  We UNPIVOT these years  into two values, a year and the value for that year.   The year we call [Years] and the value we call [Medals]  :

CREATE TABLE [dbo].[SomeData](
 [Country] [varchar](20) NULL,
 [Sport] [varchar](20) NULL,
 [2004] [int] NULL,
 [2008] [int] NULL,
 [2012] [int] NULL

INSERT [dbo].[SomeData] ([Country], [Sport], [2004], [2008], [2012]) VALUES (N'Australia', N'100m Womens', 14, 12, 3)
INSERT [dbo].[SomeData] ([Country], [Sport], [2004], [2008], [2012]) VALUES (N'Somalia', N'100m Womens', 0, 0, 0)
SELECT Country, Sport, Years, Medals
FROM [dbo].[SomeData] Main
[Medals] FOR Years IN (
) Sub

Another alternative is to utilise the latest release of the Data Explorer plug-in for Excel 2013.  This could make a lot of sense, give that your pivoted data source could be in Excel anyway.  You can find a link to the latest release of Data Explorer containing the Unpivot feature here http://blogs.msdn.com/b/dataexplorer/archive/2013/04/12/download-the-latest-data-explorer-update-unpivot-feature-and-search-experience-improvements.aspx.

Happy Unpivoting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s