This advanced SQL exercise is part of the book: Advanced SQL – Practical Techniques and Use-Cases, which includes more than 100 day-to-day SQL challenges (and solutions), need-to-know advanced features, and key concepts.

 

The following EmployeeAbsence table registers information about the absences of different employees. Each row specifies :

  • Employee ID – The employee number
  • Absence Type ID – The absence category: (2) Illness or Injury (3) Maternity/Paternity (8) Personal reasons
  • Absence Date – The date of the absence
  • Number of Hours -The number of absence hours for a specific date
CREATE TABLE EmployeeAbsence 
(employee_id int, 
 absence_type_id int, 
 num_of_hours int,
 absence_date date)
GO 

 

So, for example, we can see that :

  • On 2017-01-02, employee number 5 was absent for 9 hours due to medical reasons
  • In overall, employees number 5 was absent twice due to medical reasons (18 hours in total)
  • On 2017-12-13, employee number 7 was absent due to personal reasons
  • In overall, employee number 7 was absent three times due to personal reasons (15 hours in total)

 

 

write a query to pivot the data as follows :