Pivot Tables

-- =============================================         
-- Author:  <KHALID,,>         
-- Create date: <20 July,2020 ,>         
-- Description: <Jakaria bhai asked for District wise and DesignationWise EmployeeCount>         
-- exec SP_GetDistrictWiseEmployeeDesig_Pivot       
-- =============================================         
ALTER PROC [dbo].[SP_GetDistrictWiseEmployeeDesig_Pivot]         
AS         
BEGIN           

DECLARE --Put initial Selected data       
 @InitialData Table(       
  RowNum int IDENTITY(1,1) NOT NULL ,   
  EmployeeId bigint,     
  DistrictCode   nvarchar(50) ,
  DistrictName nvarchar(50),
  designationName nvarchar(50)
         
 )


Insert into @InitialData SELECT
d.EmployeeId ,
d.DistrictCode ,
d.DistrictName ,
d.designationName

 From
 (
SELECT -- dbo.FN_GET_EmployeeDistrict(e.DistrictId, 1) AS [Old District Name],                         
--dbo.FN_GET_EmployeeDistrict(e.DistrictId, 2) AS [Old District Code],
e.EmployeeId,
isNull(dis.District_Code, dbo.FN_GET_EmployeeDistrict(e.DistrictId, 2)) AS DistrictCode
, isNUll( dis.district_name_eng, dbo.FN_GET_EmployeeDistrict(e.DistrictId, 1)) AS DistrictName
 , ed.designationName
FROM      Employee AS e /* SELECT * FROM Office*/ LEFT JOIN             
                         EmployeeDesignation AS ed ON ed.DesignationId = e.DesignationId LEFT JOIN                         
                         OfficeDesignation od ON od.OfficeDesignationId = e.OfficeDesignationId 
Left Join                         
                         EmployeeAddress AS ea ON ea.EmployeeId = e.EmployeeId AND ea.AddressType = 'Pe' AND ea.IsActive = 1 LEFT JOIN                         
                         LgThana AS th ON th.thana_id = ea.ThanaId LEFT JOIN                         
                         LgUnion AS un ON un.union_id = ea.UnionId LEFT JOIN                         
                         District AS dis ON dis.district_id = ea.DistrictId AND dis.district_id IS NOT NULL LEFT JOIN                         
                         StateOrProvince AS sp ON sp.StateOrProvinceId = ea.StateOrProvinceId LEFT JOIN                         
                         ThanaOldByEmloyee AS toe ON e.EmployeeId = toe.EmployeeId

WHERE e.EmployeeStatus = 'A' AND ed.DesignationId <= 15 


) AS d

--SELECT * FROM @InitialData

 SELECT * FROM       
(     
    SELECT       
EmployeeId ,
DistrictCode ,
DistrictName ,
designationName
     
    FROM     
        @InitialData     
     
) t     
PIVOT(     
   COUNT(EmployeeId)     
    FOR DesignationName IN (     
        [TO],     
        [SPO],     
        [SO],     
        [PO],     
        [MD],     
[GM],     
        [DMD],     
[DGM],     
[D.P.O],     
[AGM],     
[A/C SPECIALIST],     
[NULL]     
         
  )     
) AS pivot_table;

--Order By DistrictCode Asc 

-- SELECT * FROM District
     
     
     
     
     
     
       
END       
-- END Main Loop       
--SP_GetPromotionInfo 838