UNPIVOT OPERATOR IN SQL SERVER

This article will explain about the UNPIVOT Relational Operator, introduced in SQL SERVER 2005. We can use the UNPIVOT relational operator to change a table valued expression to another table. It provides a simple mechanism in SQL Server to transform columns into rows.

Code for UNPIVOT

To understand UNPIVOT with examples, will first create a temporary table #Salary_Details with some records as available in the below image by using the below mentioned script.

TEMP TABLE #Salary_Details

CREATE TABLE #Salary_Details

CREATE TABLE #SALARY_DETAILS (EMP_ID VARCHAR(20),EMP_NAME VARCHAR(20),[SALERY(MAY)] INT, [SALERY(JUNE)] INT, [SALERY(JULY)] INT,[SALERY(AUGUST)] INT)

INSERT SOME RECORDS TO TABLE

INSERT INTO #SALARY_DETAILS VALUES ('AAA-123','AAA',2000,3000,4000,5000)
INSERT INTO #SALARY_DETAILS VALUES ('BBB-123','BBB',20000,30000,40000,50000)
INSERT INTO #SALARY_DETAILS VALUES ('CCC-123','CCC',200,300,400,500)
INSERT INTO #SALARY_DETAILS VALUES ('DDD-123','DDD',0,0,0,0)
INSERT INTO #SALARY_DETAILS VALUES ('EEE-123','EEE',NULL,NULL,NULL,NULL)

EXAMPLE 1:

In this example #Salary_Details table data is unpivoted so that we can transform the desired columns as row.

CODE FOR THE UNPIVOTE (RECORDS WITHOUT NULL VALUES)

SELECT EMP_ID, EMP_NAME, SALERY_MONTH, SALERY   
    FROM
    (       
        SELECT        
            EMP_ID,EMP_NAME,       
            [SALERY(MAY)] AS MAY,
            [SALERY(JUNE)] AS JUNE,
            [SALERY(JULY)] AS JULY,
            [SALERY(AUGUST)] AS AUGUST
        FROM   
            #SALARY_DETAILS
    )MAIN
UNPIVOT (SALERY FOR SALERY_MONTH IN(MAY,JUNE,JULY,AUGUST)) AS UNPIVOTED_COLUMNS

OUTPUT:

 

NOTE: But the problem with this code is, It will not work for the records having NULL value. So, If you are having records with null values use the below written code. It will give you the correct result.

CODE FOR THE UNPIVOTE (RECORDS WITH NULL VALUES)

SELECT EMP_ID, EMP_NAME, SALERY_MONTH, SALERY
    FROM
    (
        SELECT
            EMP_ID, EMP_NAME,
            ISNULL([SALERY(MAY)],0) AS MAY,
            ISNULL([SALERY(JUNE)],0) AS JUNE,
            ISNULL([SALERY(JULY)],0) AS JULY,
            ISNULL([SALERY(AUGUST)],0) AS AUGUST
        FROM
            #SALARY_DETAILS
    )MAIN
UNPIVOT (SALERY FOR SALERY_MONTH IN(MAY,JUNE,JULY,AUGUST)) AS UNPIVOTED_COLUMNS

OUTPUT: