What do you meant by an UnPivot element in sql server?

 Posted by Chvrsri on 12/9/2010 | Category: Sql Server Interview questions | Views: 2058 | Points: 40
Answer:

As the name implies, an UnPivot element is absolutely opposite to Pivot operation.Generally when we invoke an UnPivot operation it would turn the pivoted elements into rows. That means one row of data for every column is to be unpivoted.

It could be well explained by using a simple example.

Let us create a table first :

 CREATE TABLE EMP(

EID INT,
ENAME VARCHAR(50),
JOB VARCHAR(50),
SAL INT
)


Insert values into it :

INSERT INTO EMP1 

VALUES(1,'NARENDRA','MANAGER',10000)
INSERT INTO EMP1
VALUES(2,'SRIDHAR','ANYLYST',12000)
INSERT INTO EMP1
VALUES(3,'NAREN','CLERK',14000)
INSERT INTO EMP1
VALUES(4,'NARENDRA','MANAGER',11000)
INSERT INTO EMP1
VALUES(5,'SRIDHAR','ANYLYST',13000)
INSERT INTO EMPTABLE1
VALUES(6,'NAREN','CLERK',15000)
INSERT INTO EMP1
VALUES(7,'NARENDRA','MANAGER',12000)
INSERT INTO EMP1
VALUES(8,'SRIDHAR','ANYLYST',14000)
INSERT INTO EMP1
VALUES(9,'NAREN','CLERK',16000)
INSERT INTO EMP1
VALUES(10,'NARENDRA','MANAGER',13000)
INSERT INTO EMP1
VALUES(11,'ARAVIND','MANAGER',15000)


Now apply UnPivot property :

SELECT     EID , Property, Value

FROM (SELECT EID,
CONVERT(sql_variant,EName) AS EName,
CONVERT(sql_variant,JOB) AS JOB,
CONVERT(sql_variant,SAL) AS SAL
FROM EMP1) EMP1
UNPIVOT (Value For Property In (EName, JOB, SAL)) as UPT


Now the final output will be in this format :

------------------------------------------------
EID || Property || Value
-----------------------------------------------
1 || EName || NARENDRA
1 || JOB || MANAGER
1 || SAL || 10000
.
.
.
.

In this way it goes and seperates all the records in the table.


Source: My Own Observation | Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response