Berikut adalah penggunaan UnPivot pada Oracle :
Format :
SELECT
select_column_list
FROM table_name
UNPIVOT [INCLUDE | EXCLUDE NULLS](
unpivot_clause
unpivot_for_clause
unpivot_in_clause
);
1. Buat table
create table STUDENT_MARKS
(
STUDENT_ID NUMBER,
STUDENT_NAME VARCHAR2(100),
TELUGU NUMBER(3),
HINDI NUMBER(3),
ENGLISH NUMBER(3),
MATHS NUMBER(3),
PHYS NUMBER(3),
CHEM NUMBER(3),
MARKS NUMBER default "TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM",
PERCENTAGE NUMBER default ("TELUGU"+"HINDI"+"ENGLISH"+"MATHS"+"PHYS"+"CHEM")*100/600
);
2. Isikan data
insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (1, 'VISWANATH', 87, 84, 74, 97, 74, 74);
insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (2, 'VINITH', 88, 83, 79, 100, 79, 89);
insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (3, 'RAVI', 87, 52, 59, 100, 98, 68);
insert into STUDENT_MARKS (STUDENT_ID, STUDENT_NAME, TELUGU, HINDI, ENGLISH, MATHS, PHYS, CHEM)
values (4, 'RAGHU', 88, 79, 85, 89, 69, 96);
3. Tampilkan Data
SELECT *
FROM STUDENT_MARKS;
Output :
STUDENT_ID STUDENT_NAME TELUGU HINDI ENGLISH MATHS PHYS CHEM MARKS PERCENTAGE
1 VISWANATH 87 84 74 97 74 74 490 81.6666666666667
2 VINITH 88 83 79 100 79 89 518 86.3333333333333
3 RAVI 87 52 59 100 98 68 464 77.3333333333333
4 RAGHU 88 79 85 89 69 96 506 84.3333333333333
4. Query UnPivot
SELECT STUDENT_ID,
STUDENT_NAME,
SUBJECT_NAME,
SUB_MARKS
FROM STUDENT_MARKS
UNPIVOT
(SUB_MARKS -- unpivot_clause
FOR SUBJECT_NAME -- unpivot_for_clause
IN( -- unpivot_in_clause
TELUGU AS 'TELUGU',
HINDI AS 'HINDI',
ENGLISH AS 'ENGLISH',
MATHS AS 'MATHS',
PHYS AS 'PHYSICS',
CHEM AS 'CHEMISTRY'
)
);
Output :
