如何根据条件选择列？
问题描述：
嗨
如何将条件应用于列并水平获取值在单个表中
什么我试过了：
Hi
How to apply condition to the column and get the values horizontally Which is in a single table
What I have tried:
<pre>SELECT COUNT(EmpID) AS NewJoinees FROM EmployeeDetails AS EmployeeDetails_7 WHERE
(DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ) = 02) and datepart(mm,DOJ)<='02' and status=0
SELECT COUNT(EmpID) AS Resigned FROM EmployeeDetails AS EmployeeDetails_7 WHERE (DATEPART(yyyy, deactivate) = 2017) AND (DATEPART(mm, deactivate) <= '04') and Status=1
SELECT COUNT(EmpID) AS TotalEmployee FROM EmployeeDetails AS EmployeeDetails_7 where (datepart(yyyy,DOJ) between 2005 and 2017)and datepart(mm,DOJ) <='12' and status=0
答
Below code should work as per your requirement
;WITH CTE AS(
SELECT TotalEmployee=case when (datepart(yyyy,DOJ) between 2005 and 2017)and datepart(mm,DOJ) <='12' and status=0 THEN COUNT(EmpID) ELSE 0 END,
Resigned=case when (DATEPART(yyyy, deactivate) = 2017) AND (DATEPART(mm, deactivate) <= '04') and Status=1 THEN COUNT(EmpID) ELSE 0 END,
NewJoinees=case when (DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ) = 02) and datepart(mm,DOJ)<='02' and status=0 THEN COUNT(EmpID) ELSE 0 END
FROM EmployeeDetails AS EmployeeDetails_7
group by DOJ,deactivate,status
)
SELECT SUM(TotalEmployee) TotalEmployee,SUM(Resigned)Resigned,SUM(NewJoinees) NewJoinees FROM CTE