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 :
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
