How to use the UNPIVOT Function in SQL
Do you want to learn how to unpivot a table in SQL Server?
Unpivot is a handy operator that converts columns into rows, making your data more normalized and readable.
Quite often we are presented with these type of outputs from various systems by default
This can make the table hard to read, analyze, 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)
To find out more, check out this blog post
https://www.selectdistinct.co.uk/2023...
#sql #unpivot #datatransformation #sqlserver #blogpost
Need help with your business analytics?
🌐 WEBSITE: https://www.selectdistinct.co.uk/
🧑💼 LINKEDIN: / select-distinct
📚 FACEBOOK: / selectdistinctconsulting
▶️ SUBSCRIBE TO OUR CHANNEL: / @selectdistinctanalytics
📧 EMAIL: info (at) selectdistinct.co.uk
♪ TIKTOK: / selectdistinct
#SQLTips
Music by www.bensound.com
if you want to follow along you can use this code to generate the data
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)
GO