How to Unpivot a Table in SQL Server
The Power of the Unpivot function in SQL : How to Convert Columns into Rows in SQL Server with One Simple Operator
Have you ever encountered a table that has too many columns and not enough rows?
A table that looks like this:
This is an example of a pivoted table, where the values of one column (Month) are spread across multiple columns.
Quite often we are presented with these type of outputs from various systems by default usually as CSV exports from SAAS tools
This can make the table hard to read, analyse, and join with other tables.
So how can we transform this table into a more normalized and readable form?
One way is to use the UNPIVOT operator in SQL Server.
UNPIVOT is a relational operator that converts columns of a table-valued expression into column values. It is the opposite of PIVOT, which rotates rows into columns.
To unpivot a table in SQL Server, we need to specify three things:
- The column that remains unchanged in the output (Year)
- The new column that holds the names of the pivoted columns (Month)
- The new column that holds the values of the pivoted columns (Days)
We also need to provide the source table and the list of columns that we want to unpivot. The syntax is:
SELECT <non-pivoted column>, <unpivoted column>, <value column>
FROM <source table>
UNPIVOT (<value column> FOR <unpivoted column> IN (<pivoted columns>)) AS <alias>
select Year, Month, days from [dbo].[UNPIVOT_Example] UNPIVOT (days for Month in ([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December]) )as dpm_unpivot
as you can see, we have converted the twelve columns (January to December) into two columns (Month and Days), and increased the number of rows accordingly. This makes the table more normalized and easier to work with.
If you want to try this you can use this code to generate a test data set for you to work with
CREATE TABLE [dbo].[UNPIVOT_Example](
[year] [int] NULL,
[January] [int] NULL,
[February] [int] NULL,
[March] [int] NULL,
[April] [int] NULL,
[May] [int] NULL,
[June] [int] NULL,
[July] [int] NULL,
[August] [int] NULL,
[September] [int] NULL,
[October] [int] NULL,
[November] [int] NULL,
[December] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[UNPIVOT_Example] ([year], [January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December])
VALUES
(2018, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31),
(2019, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31),
(2020, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31),
(2021, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31),
(2022, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
GOBenefits of unpivoting a table
- It reduces redundancy and improves data integrity
- It simplifies queries and joins
- It enables aggregation and analysis by different dimensions
- It makes the data more readable and understandable
Conclusion
The UNPIVOT function in SQL is a powerful and useful operator that can help you transform your data in SQL Server. You can use it to normalize your tables, or to reverse the effect of a PIVOT operation. You can also combine it with other operators and functions to perform more complex transformations.
I hope you enjoyed this blog post and learned something new. If you have any questions or feedback, please leave a comment below. Thanks for reading!
Subscribe to our channel to see more tips and timesavers
Select Distinct YouTube Channel
Or you can find other useful SQL, Power BI or other business analytics timesavers in our Blog
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.
Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
