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

Before UNPIVOT table

After UNPIVOT

UNPIVOT sample in SQL Server

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

Before UNPIVOT multiple columns table

 

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)

After UNPIVOT multiple columns table