How to UNPIVOT table in SQL Server / UNPIVOT table example in SQL Server
What is PIVOT and UNPIVOT operator in SQL Server
We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
Please go through our previous post for PIVOT sample in SQL
Before UNPIVOT
After UNPIVOT
A simple UNPIVOT sample in SQL Server
Here we are going to demonstrate a very simple UNPIVOT sample without any complexity. We are having a table named EmploymentHistory and its containing employee name with previous company name that particular employee worked.
Table Structure For showing simple UNPIVOT sample
CREATE TABLE EmploymentHistory (Id INT, Employee VARCHAR(500), Company1 VARCHAR(500), Company2 VARCHAR(500), Company3 VARCHAR(500) ) GO -- Load Sample data INSERT INTO EmploymentHistory SELECT 1, 'John', 'Tata Motors', 'Ashok Leyland', 'Suzuki' UNION ALL SELECT 2, 'Kate', 'Airtel', 'Vodafone', 'Tata Docomo' UNION ALL SELECT 3, 'Sam', 'Hercules', 'Hero', 'Atlas' GO
In order to UNPIVOT above table we can use below mentioned script. The result should be as Employee with each company as a single row.
SELECT Id, ROW_NUMBER()OVER(Order By ID) as NewID, Employee, Company FROM ( SELECT Id, Employee, Company1, Company2, Company3 FROM EmploymentHistory ) Main UNPIVOT ( Company FOR Companies IN (Company1, Company2, Company3) ) Sub
UNPIVOT Table with multiple columns
Now we are going to UNPIVOT table with multiple columns. Suppose we are having a table with Employee name, list of company names that particular employee worked and corresponding cities also. The table structure like below.
CREATE TABLE EmploymentHistoryWithCity (Id INT, Employee VARCHAR(500), Company1 VARCHAR(500), Company2 VARCHAR(500), Company3 VARCHAR(500), City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500) ) GO -- Load Sample data INSERT INTO EmploymentHistoryWithCity SELECT 1, 'John', 'Tata Motors', 'Ashok Leyland', 'Suzuki', 'Mumbai', 'Kolkata', 'Delhi' UNION ALL SELECT 2, 'Kate', 'Airtel', 'Vodafone', 'Tata Docomo', 'Chennai', 'Kolkata', 'Banglore' UNION ALL SELECT 3, 'Sam', 'Hercules', 'Hero', 'Atlas', 'Delhi', 'Mumbai', 'Banglore' GO
In order to UNPIVOT above table we can use below mentioned script. The result should be as Employee name with Company1 and City1 as a first row, Employee Name with Company2 and City2 as second row and so on SELECT Id, ROW_NUMBER()OVER(Order By ID) as NewID, Employee, Company, City FROM ( SELECT Id, Employee, Company1, Company2, Company3, City1, City2, City3 FROM EmploymentHistoryWithCity ) Main UNPIVOT ( Company FOR companies IN (Company1, Company2, Company3) ) Sup UNPIVOT ( City For Cities IN (City1, City2, City3 ) ) Ct WHERE RIGHT(companies,1) = RIGHT(Cities,1)
Explained with real time example,
http://www.negablog.com/2013/01/unpivot-table-in-sql-server.html
http://www.negablog.com/2013/01/pivot-table-using-sql-server.html
Reblogged this on Yet another SQL Server DBA… and commented:
Recently I’ve had a similar case when I needed to unpivot multiple columns at the same time. It turned out that running multiple UNPIVOTs is actual slower and creates more complex execution plans than a single CROSS APPLY. The difference is getting more significant as row count grows.
Greetings! Quick question that’s completely off topic.
Do you know how to make your site mobile friendly?
My website looks weird when browsing from my apple iphone.
I’m trying to find a theme or plugin that might be able
to fix this issue. If you have any recommendations, please share.
Many thanks!
I enjoy looking through a post that can make people think.
Also, many thanks for allowing me to comment!
Photovoltaic solar panels ontario faculties are grimed significantly, so you
feature 6 and so on. With up technology, efficiency
has droped off from five fts long, so solar panels ontario can you do.
As we go farther, envisage your monthly fair energy usage distinctive of the powerfulness cut.
Why aren’t masses making this as good. A North-East or North West
fronting roof would be to buy. in the meantime, front of the information
about the view.
Hi, after reading this remarkable piece of writing i am as well delighted to share my familiarity here with colleagues.
We know it is human nature to devalue the things we receive for free.
In case fire or explosion does occur, immediately smother
the fire with a thick blanket or non-flammable material to remove oxygen since fire cannot be sustained in the
absence of this element. If you haven’t yet, open the box your
motherboard came in and use the manual to make sure you have everything that should be there.
We know it is human nature to devalue the things we receive
for free. Building a shed on your own is beneficial in many ways.
If you haven’t yet, open the box your motherboard came in and
use the manual to make sure you have everything that should be there.
Every weekend i used to visit this web page, because i wish
for enjoyment, for the reason that this this site conations
in fact fastidious funny stuff too.