Hello Everyone 
Today i will teach you one of the famous interviewed coding question
i.e how you can convert rows into columns with using special function PIVOT() and without using specials function. 

This code can help both to freshers and experienced persons in the IT industry.

Let's start with creating a table.

 
CREATE TABLE result( 
std_id INT, 
course_id VARCHAR(20), 
mark_type VARCHAR(20), 
marks INT);

-- This is how you insert multiple data to table in Oracle

insert into result (std_id,course_id,mark_type,marks) 
with p as (  
select 6060,'MATH-401','Internal_Exam',15 from dual union all
select 6060,'MATH-401','Mid_Term_Exam',20 from dual union all 
select 6060,'MATH-401','Final_Exam',35 from dual union all
select 6061,'ENGLISH-403','Internal_Exam',17 from dual union all
select 6061,'ENGLISH-403','Mid_Term_Exam',15 from dual union all
select 6061,'ENGLISH-403','Final_Exam',30 from dual) select * from p;
Output:





Now we will convert MARK_TYPE rows data to columns and show related MARKS.
	
 
select * from ( select  
std_id,course_id,mark_type,marks from result) 
pivot 
(max(marks) for mark_type in ('Internal_Exam','Mid_Term_Exam','Final_Exam'));
Output:



Alternatively, without using PIVOT() function we will get desired result using CASE THEN.
	
 
SELECT std_id, course_id, 
MAX( CASE WHEN mark_type = 'Internal_Exam' THEN marks END) as "Internal_Exam", 
MAX( CASE WHEN mark_type = 'Mid_Term_Exam' THEN marks END) as "Mid_Term_Exam", 
MAX( CASE WHEN mark_type = 'Final_Exam' THEN marks END) as "Finale_Exam" 
FROM result 
GROUP BY std_id, course_id 
ORDER BY std_id, course_id;
Output: 

Yeah!! Now you know how to code this type of questions if an interviewer asked during the interview.


Thanks, if you learn something today, please do comment, share and like my pages.