Advertisements
Home > SQL > How to UNPIVOT table in SQL Server / UNPIVOT table example in SQL Server

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

UNPIVOT sample in SQL Server

Before UNPIVOT table

After UNPIVOT

UNPIVOT sample in SQL Server

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
UNPIVOT sample in SQL Server

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)
UNPIVOT sample in SQL Server

After UNPIVOT multiple columns table 

 

Advertisements
  1. Szymon Wójcik
    August 8, 2013 at 4:06 pm

    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.

  2. March 21, 2014 at 11:17 am

    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!

  3. April 4, 2014 at 9:23 pm

    I enjoy looking through a post that can make people think.
    Also, many thanks for allowing me to comment!

  4. April 20, 2014 at 7:16 am

    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.

  5. April 20, 2014 at 7:19 pm

    Hi, after reading this remarkable piece of writing i am as well delighted to share my familiarity here with colleagues.

  6. April 23, 2014 at 2:37 pm

    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.

  7. April 23, 2014 at 3:11 pm

    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.

  8. June 4, 2014 at 1:06 pm

    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.

  1. July 12, 2012 at 7:21 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: