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.