הטבלה הבאה מציגה אינפורמציה על חיסורי עובדים שונים. כל שורה מייצגת את:
- מס' העובד (EmployeeID)
- קטגוריית החיסור – (2) סיבה רפואית (3) סיבה מקצועית (8) סיבה אישית (absence_type_id)
- התאריך בו העובד החסיר שעות (absence_date)
- מס' השעות אותן הוא החסיר באותו התאריך (num_of_hours)
CREATE TABLE EmployeeAbsence (employee_id int, absence_type_id int, num_of_hours int, absence_date date) GO
לדוגמא, ניתן לראות כי :
- בתאריך 2017-01-02 עובד מס' 5 החסיר 9 שעות בשל סיבות רפואיות
- עובד מס' 5 החסיר פעמיים בשל סיבות רפואיות (18 שעות בסך הכל)
- בתאריך 2017-12-13 עובד מס' 7 החסיר 5 שעות בשל סיבות אישיות
- עובד מס' 7 החסיר 3 פעמים בשל סיבות אישיות (15 שעות בסך הכל)
כתבו שאילתא אשר מציגה את נתוני הטבלה בצורה הבאה :
מוזמנים לכתוב את הפתרונות שלכם. את שלי אעלה מתישהו בעתיד הקרוב 😉
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