CODEWITHSUNDEEP
oracle-database
Neth
Neth

Reputation: 25

How to display row data as column in Oracle

I want to display row values as column values. and also display the final total value at end of the table.

To do that I'm using the below data set.

enter image description here

I want to set this data in the column side.

enter image description here

I used this SQL query to do that. But I don't know how to get Hours Total Column

select *   
from 
    (select EMP_NO,SUM(Hours) total
     from Employee_Attendence
     group by EMP_NO) 
pivot
    (sum(total) 
         for WAGE_Type in ('Absence', 'Normal'))

Final output should display as below.

Select EMP_NO, Absence, Normal, Total
From 
    (select *
     from 
         (select EMP_NO, sum(Hours) total
          from Employee_Attendence
          group by EMP_NO) 
     pivot
         (sum(total) 
              for WAGE_Type in ('Absence', 'Normal'))
)

Upvotes: 0

Views: 1765

Answers (1)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

SELECT emp_id, sum( CASE  WHEN wage_type = 'Absence' THEN Hours END ) Absence,
               sum( CASE  WHEN wage_type = 'Normal' THEN Hours END ) Normal,
               sum( hours ) "Hours total"
FROM employee_attendance
GROUP BY emp_id;

Output

EMP_ID  ABSENCE NORMAL HOURS TOTAL
4000    8       32     40

Upvotes: 3

Related Questions