Most of the times and for summarizing information, we convert data to  pivot tables, but sometimes we need to unpivot data. For example, when we want to create a fact table in our Star Schema model.

For this demonstration, I used the data from the previous post as a reference. As you may know, the way that the data is collected in an OLTP system is different from an OLAP database. In this example our source data is gas price in different states in US as below:

And we need to convert it to the following format:

There are many ways to do this, and here I want to show you three methods in popular tools.

1-Using T-SQL in SQL Server Management Studio (SSMS)

By writing the following query and store the result such as a Stored Procedure:

You can also add parameters to the query as needed.

2-In Power Query in Power BI or Excel:

Select the table and columns that you want to unpivot:

Then go to the transform page and press the “Unpivot columns”:

The result can be as follows. You may like to change the column names to the friendly names.

3-In SQL Server Integration Services (SSIS)

Create a Data Flow task in the Control Flow section. Then define your data source and destination in the Data Flow and drag an Unpivot transformation as below:

In this example, Unpivot transformation editor window setting was as per below:

Depending on your data destination, you may define either a new table or use the result for another transformation.

In this example, source and destination were located in SQL Server database and data was mapped as follows:

Summary

Depending on the volume of your data, you may select one of these methods. It is generally advised that you prepare the data in the source system that can be a SQL server database. However, you can also shape it in the Power Query Editor in either Power BI or Excel.