CREATE TABLE EmployeeAbsence 
(employee_id int, 
 absence_type_id int, 
 num_of_hours int,
 absence_date date)
GO 

INSERT INTO EmployeeAbsence VALUES 
(5,	2,	9,	 '1/2/2017'),
(5,	2,	9,	 '1/8/2017'),
(5,	3,	6,	 '1/5/2017'),
(6,	2,	6	,'1/2/2017'),
(6,	3,	6	,'1/5/2017'),
(6,	8,	9	,'1/8/2017'),
(7,	2,	2,	'12/7/2017'),
(7,	8,	5,	'12/13/2017'),
(7,	8,	1,	'12/8/2017'),
(7,	8,	9,  '12/7/2017'),
(8,	2,	3,	'9/14/2017'),
(8,	3,	2,	'9/15/2017'),
(8,	8,	9,  '9/18/2017')
GO

SELECT * FROM EmployeeAbsence

---------------------------------------------------
-- Solution 1 - By Michael Shapira
---------------------------------------------------

SELECT employee_id
, SUM(IIF(absence_type_id = 2, 1, 0)) num_of_absence_type_2, SUM(IIF(absence_type_id = 2, num_of_hours, 0)) num_of_hours
, SUM(IIF(absence_type_id = 3, 1, 0)) num_of_absence_type_3, SUM(IIF(absence_type_id = 3, num_of_hours, 0)) num_of_hours
, SUM(IIF(absence_type_id = 8, 1, 0)) num_of_absence_type_8, SUM(IIF(absence_type_id = 8, num_of_hours, 0)) num_of_hours
FROM EmployeeAbsence
GROUP BY employee_id

---------------------------------------------------
-- Solution 2 - By Hannan Kravitz
---------------------------------------------------

/*Variable declaration for the dynamic part of the pivot*/
DECLARE @Cols nvarchar(200) =”
DECLARE @Hdrs1 nvarchar(200)= ”
DECLARE @Hdrs2 nvarchar(200)= ”
DECLARE @FinalHdrs1 nvarchar(200)=”
DECLARE @FinalHdrs2 nvarchar(200)=”
DECLARE @Pvt nvarchar(4000)
 
/*Columns and Headers preparation*/
SELECT @Cols+= ',' + QUOTENAME(CONVERT(NVARCHAR(10),absence_type_id))
FROM EmployeeAbsence
GROUP BY absence_type_id
SET @Cols = STUFF(@cols,1,1,”)
 
SELECT @Hdrs1+= ',' +QUOTENAME(CONVERT(NVARCHAR(10),absence_type_id)) + ' AS num_of_absence_type_' + CONVERT(NVARCHAR(10),absence_type_id)
FROM EmployeeAbsence
GROUP BY absence_type_id
SET @Hdrs1 = STUFF(@Hdrs1,1,1,”)
 
SELECT @Hdrs2+= ',' +QUOTENAME(CONVERT(NVARCHAR(10),absence_type_id)) + ' AS num_of_hours_' + CONVERT(NVARCHAR(10),absence_type_id)
FROM EmployeeAbsence
GROUP BY absence_type_id
SET @Hdrs2 = STUFF(@Hdrs2,1,1,”)
 
SELECT @FinalHdrs1+= ',num_of_absence_type_' + CONVERT(NVARCHAR(10),absence_type_id)
FROM EmployeeAbsence
GROUP BY absence_type_id
SET @FinalHdrs1 = STUFF(@FinalHdrs1,1,1,”)
 
SELECT @FinalHdrs2+= ',num_of_hours_' + CONVERT(NVARCHAR(10),absence_type_id)
FROM EmployeeAbsence
GROUP BY absence_type_id
SET @FinalHdrs2 = STUFF(@FinalHdrs2,1,1,”)
 
/*Setting the pivot statement*/
SET @Pvt =
'
;WITH Hrs AS
(
SELECT employee_id , '+@Hdrs2+'
FROM
(
SELECT employee_id , num_of_hours , absence_type_id
FROM EmployeeAbsence A
) Main
pivot
(
sum(num_of_hours) for absence_type_id in ('+@Cols+')
) y
)
, Absence as
(
SELECT employee_id ,'+@Hdrs1+'
FROM
(
SELECT employee_id , num_of_hours , absence_type_id
FROM EmployeeAbsence A
) Main
pivot
(
COUNT(num_of_hours) for absence_type_id in ('+@Cols+')
) y
)
 
SELECT Hrs.employee_id , '+@FinalHdrs1+' , '+@FinalHdrs2+'
FROM Hrs
JOIN Absence
ON Hrs.employee_id = Absence.employee_id
'
/*executing the pvt statement*/
exec sp_executesql @Pvt

---------------------------------------------------
-- Solution 3 - By Hannan Kravitz
---------------------------------------------------

;WITH Agg AS
(
SELECT employee_id ,  Num_Of_Absence_Type , Num_Of_Hours , absence_type_id 
FROM
(
SELECT employee_id , COUNT(employee_id) OVER (PARTITION BY employee_id , absence_type_id) Num_Of_Absence_Type ,
                     SUM(Num_of_Hours) OVER (PARTITION BY employee_id , absence_type_id) Num_Of_Hours,
                     absence_type_id ,
                     ROW_NUMBER () OVER (PARTITION BY employee_id , absence_type_id ORDER BY employee_id) Rn
FROM EmployeeAbsence
) y
WHERE Rn = 1 
)
 
SELECT employee_id , [Num_Of_Hours_2],[Num_Of_Absence_Type_2],[Num_Of_Hours_3],[Num_Of_Absence_Type_3],[Num_Of_Hours_8],[Num_Of_Absence_Type_8]
FROM
(
SELECT employee_id , 
Col+'_'+CONVERT(NVARCHAR(10),absence_type_id) New_Col , val
FROM agg
    CROSS APPLY 
        (
        VALUES ('Num_Of_Hours' , Num_Of_Hours)  , 
               ('Num_Of_Absence_Type' , Num_Of_Absence_Type)
        ) x(Col, val)
) Main
PIVOT 
    (
    MAX(val) FOR New_Col in ([Num_Of_Hours_2],[Num_Of_Absence_Type_2],[Num_Of_Hours_3],[Num_Of_Absence_Type_3],[Num_Of_Hours_8],[Num_Of_Absence_Type_8])
    )   p

---------------------------------------------------
-- Solution 4 - By Moshe Adad
---------------------------------------------------

select employee_id
    ,count(case when absence_type_id = 2 then 1 end) as Num_of_absence_type2
    ,Sum(case when absence_type_id = 2 then num_of_hours else 0 end ) as num_of_hours
    ,count(case when absence_type_id = 3 then 1 end) as Num_of_absence_type3
    ,Sum(case when absence_type_id = 3 then num_of_hours else 0 end ) as num_of_hours
    ,count(case when absence_type_id = 8 then 1 end) as Num_of_absence_type8
    ,Sum(case when absence_type_id = 8 then num_of_hours else 0 end ) as num_of_hours
from EmployeeAbsence
group by employee_id
order by employee_id asc

---------------------------------------------------
-- Solution 5 - By Moshe Adad
---------------------------------------------------

SELECT T1.[employee_id]
      ,T1.Num_of_absence_type2
      ,T2.num_of_hours_type2
      ,T1.Num_of_absence_type3
      ,T2.num_of_hours_type3
      ,T1.Num_of_absence_type8
      ,T2.num_of_hours_type8
 
FROM (SELECT [employee_id] 
     , [2] AS Num_of_absence_type2 
     , [3] AS Num_of_absence_type3 
     , [8] AS Num_of_absence_type8
 
FROM (  select [employee_id] , [absence_type_id] 
        FROM [dbo].[EmployeeAbsence]
      ) EA
PIVOT(COUNT([absence_type_id]) FOR [absence_type_id] IN([2],[3],[8]) ) AS P) T1
 
INNER JOIN
(SELECT [employee_id] 
     , [2] AS num_of_hours_type2
     , [3] AS num_of_hours_type3
     , [8] AS num_of_hours_type8
 
FROM (  select [employee_id] , [absence_type_id] ,[num_of_hours]
        FROM [dbo].[EmployeeAbsence]
      ) EA
PIVOT(SUM([num_of_hours]) FOR [absence_type_id] IN([2],[3],[8]) ) AS P ) T2
ON T1.[employee_id] = T2.[employee_id]

---------------------------------------------------
-- Solution 6 - By Elad Peleg
---------------------------------------------------

;WITH absence_data_2 AS
(SELECT employee_id, COUNT(*) num_of_absence_type_2, SUM(num_of_hours) num_of_hours
FROM EmployeeAbsence
WHERE absence_type_id = 2
GROUP BY employee_id, absence_type_id),
absence_data_3 AS
(SELECT employee_id, COUNT(*) num_of_absence_type_3, SUM(num_of_hours) num_of_hours
FROM EmployeeAbsence
WHERE absence_type_id = 3
GROUP BY employee_id, absence_type_id),
absence_data_8 AS
(SELECT employee_id, COUNT(*) num_of_absence_type_8, SUM(num_of_hours) num_of_hours
FROM EmployeeAbsence
WHERE absence_type_id = 8
GROUP BY employee_id, absence_type_id)
SELECT ad2.employee_id, 
  ISNULL(ad2.num_of_absence_type_2, 0) num_of_absence_type_2, 
  ad2.num_of_hours,
  ISNULL(ad3.num_of_absence_type_3, 0) num_of_absence_type_3,
  ad3.num_of_hours,
  ISNULL(ad8.num_of_absence_type_8, 0) num_of_absence_type_8,
  ad8.num_of_hours
FROM absence_data_2 ad2 FULL JOIN absence_data_3 ad3
ON ad2.employee_id = ad3.employee_id 
FULL JOIN absence_data_8 ad8
ON ad2.employee_id = ad8.employee_id

---------------------------------------------------
-- Solution 7 - By Hannan Kravitz
---------------------------------------------------

SET NOCOUNT ON
IF OBJECT_ID ('tempdb..##Emp_Join') IS NOT NULL DROP TABLE ##Emp_Join
DECLARE @Hdr NVARCHAR(MAX) =''
DECLARE @Sql NVARCHAR(MAX)=''
DECLARE @Command NVARCHAR(MAX)
DECLARE @Dyn_Abs_Type_Id table (absence_type_id int)
 
CREATE TABLE ##Emp_Join (employee_id INT );
 
INSERT INTO @Dyn_Abs_Type_Id
SELECT DISTINCT absence_type_id
FROM EmployeeAbsence
 
INSERT INTO ##Emp_Join
SELECT DISTINCT employee_id
FROM EmployeeAbsence
 
SELECT @Hdr+= ','+'a_' +CONVERT(VARCHAR(10),absence_type_id)+'.*'
FROM @Dyn_Abs_Type_Id
 
SET @Hdr = 'SELECT EJ.employee_id ' +@Hdr + CHAR(10) +
'from ##Emp_Join EJ'
 
SELECT @Sql +=' outer apply ('+CHAR(10)+' select
    count(case when absence_type_id = '+CONVERT(VARCHAR(10),absence_type_id)+' then 1 end) as Num_of_absence_type_'+CONVERT(VARCHAR(10),absence_type_id)+'
    ,Sum(case when absence_type_id = '+CONVERT(VARCHAR(10),absence_type_id)+' then num_of_hours else 0 end ) as num_of_hours_'+CONVERT(VARCHAR(10),absence_type_id)+'
from EmployeeAbsence
where absence_type_id = '+CONVERT(VARCHAR(10),absence_type_id)+'
and EmployeeAbsence.employee_id = ej.employee_id
group by employee_id'+CHAR(10)+
') a_'+CONVERT(VARCHAR(10),absence_type_id)+char(10)
 
FROM @Dyn_Abs_Type_Id
SET @Command = @Hdr + @Sql
PRINT @Command
EXEC sp_executesql @Command