Unpivot Columns in Excel and SQL SERVER

Welcome to Excel Jet Consult blog post. In this video, I demo how to unpivot columns in Excel and SQL Server. If you enjoy this video, share, like, comment and subscribe


Kindly watch the video tutorial below

Unpivot Columns in Power Query

Unpivot Columns simply means to rotate dataset from columns into rows. We have the sample sales data that is officially formatted as an Excel Tables using CTRL + L. You can format using the traditional CTRL + T after selecting a single cell within the dataset. The goal is to have three columns for Account Manager, Month, Sales Amount

Next, we need to get the data into the Power Query. To do that,

Right-click in any cell and select Get Data from Sheet or Table/Range


In the Get & Transform Data of the Data tab, select From Sheet or Table/Range

The Power Query editor opens as seen below

Next, we select the Account Manager column

Right-click and select Unpivot other columns

From the caption below, the dataset is unpivoted. Now, we have the 3 columns. So easy

We can then load the data to Excel or use it in the data model

Unpivot using SQL Server

We want to achieve the same result using SQL Server. In the screenshot below, I wrote a simple Select all statement to retrieve the data from my Database

The goal remains the same. We are are going to write relatively advanced query and subquery as seen below

select [Acct Managers], Month, Sales from
(select [Acct Managers], January, February, March, April, May, June,
July, August, September, October, November, December from Sales) SpecialData
(sales for month in (January, February, March, April, May, June,
July, August, September, October, November, December)) as FinalResult

Select the query and click on Execute or F5 to run. From the capture below, the dataset has been rotated and we have three columns and exactly the result we had in Excel. Be sure to watch the video for better explanation

See you in the next tutorial

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *