UNPIVOT in SQL Server

sql server convert columns to rows in a table
sql server transpose columns to rows
unpivot in sql server example
sql server unpivot example

In this video we will discuss UNPIVOT operator in SQL Server.

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

PIVOT operator turns ROWS into COLUMNS, where as UNPIVOT turns COLUMNS into ROWS.

We dicussed PIVOT operator in Part 54 of SQL Server tutorial. Please watch Part 54 before proceeding.

Let us understand UNPIVOT with an example. We will use the following tblProductSales table in this demo.

SQL Script to create tblProductSales table
Create Table tblProductSales
(
SalesAgent nvarchar(50),
India int,
US int,
UK int
)
Go

Insert into tblProductSales values ('David', 960, 520, 360)
Insert into tblProductSales values ('John', 970, 540, 800)
Go

Write a query to turn COLUMNS into ROWS.

SELECT SalesAgent, Country, SalesAmount
FROM tblProductSales
UNPIVOT
(
SalesAmount
FOR Country IN (India, US ,UK)
) AS UnpivotExample

Text version of the video

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

[mosesamponsah]

Very simple procedure. Thank you for sharing

[krzysztofs]

Thank U for educating community! You are master of SQL Server and the way of your style of teaching is just superb! Best regards from Poland ! Keep goin' please!

[Ngen]

Extremely clear and concise instructions. Thanks!

[lewisshaw]

Thanks for this, much easier to understand via video then the written word.

[nehasingh-uegi]

This is just amazing :) Part 55* for pivot in sql server

[LUPECARREON]

Thank you Sr. You are making my life much easier :)

[lahubhawar]

Thank you very much for such a detailed and clear explanation

[jasons]

Hello Venkat, your videos are great and have helped me a great deal so I thank you! I was wondering if you will be doing a video on using Client Statistics and Execution Plans soon?

[AmitKumar-pcly]

Excellent Video ! Well Explained Venkat !

[CalifGirl]

Thanks.  Very clear explanation.  I was confused on how to use UNPIVOT until I watched your video.

[gp]

Thank you for this video Venkat. Is there a way to dynamically use the Upivot operator?

[sarumathi]

Am getting Error While executing the query like Sub query does not contain the unpivoting column(s) 'India, US, UK', mentioned in the UNPIVOT operator.

[KevinNguyenSESE]

Thank you for this great video. Might I ask a question? Suppose there are two types of fields that needs to be unpivoted. For your example you unpivot three countries into a new field 'country'. But let's suppose for each of those three country there is a field indicating market strength. So in my result table I would like {sales agent; country; marketstrength; salesamount}. The method to do this in excel requires concatenation then splitting. What is the SQL method? I am trying to automate some data transformation for analysis. Thank you for any help.

[zhussupbekzhidebay]

Thanks a lot! It helped me solve problem!

[murlimohanmahalanka]

Hi venkat .. why u have use alias name for unpivot i.e unpivotexample . where it is used.

[TheCarloszoom]

Hi. I have a table and by doing the selection (SELECT `value`, `title`FROM `jos_facileforms_subrecords` WHERE `record`=80), I get the value and title, each one being a column with records below them. How would it apply to make the title values all the rows and the value records the row below that?
I appreciate your help.
Regards,

[johnnyvlee]

That was a very clear example, which is great. But could you go a bit more in-depth with more complex examples? For example, I have a case where I want to Unpivot 36 columns in a table into 36 rows with the column name and the value from that column, but the data types in each column are not identical.

[girishggirishg]

Can anyone suggest, what is the alternative for this pivot/unpivot method in MySQL?

[binbinbinvininfo]

Hello after this how can I save the unpivoted table in SQL Server?

[venudamarla]

Hello Venkat. I am trying to get calendar weeks as column names weekending is sunday for 1 year. if the month has started again it need to start as 1st week of that month
example: Jan has ( 6 weeks (1, 2-8, 9-15, 16-22, 23-39, 30-31)) in Feb has 5 weeks
January: 123456, February: 6, 7, 8 instead of that 12345
I want this results because i am getting count results based on month, weeks how to write query for my requirement