以Case...When...Then...End分列输出

SELECT 日期, 姓名, 

(CASE 出勤状态 WHEN '白班' THEN '○' END) AS 白班, (CASE 出勤状态 WHEN '夜班' THEN '○' END) AS 夜班, 

(CASE 出勤状态 WHEN '休息' THEN '○' END) AS 休息, (CASE 出勤状态 WHEN '调休' THEN '○' END) AS 调休, 

(CASE 出勤状态 WHEN '病假' THEN '○' END) AS 病假, (CASE 出勤状态 WHEN '事假' THEN '○' END) AS 事假, 

(CASE 出勤状态 WHEN '迟到' THEN '○' END) AS 迟到, (CASE 出勤状态 WHEN '早退' THEN '○' END) AS 早退, 

(CASE 出勤状态 WHEN '旷工' THEN '○' END) AS 旷工, (CASE 出勤状态 WHEN '其他' THEN '○' END) AS 其他, 部门, 身份证号, id FROM dbo.人事_考勤表 WHERE (部门 = @部门) AND (日期 = @日期)

'按出勤状态统计某部门每天的出勤数据 SELECT 日期, 部门, count(CASE 出勤状态 WHEN '白班' THEN '○' END) AS 白班, count(CASE 出勤状态 WHEN '夜班' THEN '○' END) AS 夜班, count(CASE 出勤状态 WHEN '休息' THEN '○' END) AS 休息, count(CASE 出勤状态 WHEN '调休' THEN '○' END) AS 调休, count(CASE 出勤状态 WHEN '病假' THEN '○' END) AS 病假, count(CASE 出勤状态 WHEN '事假' THEN '○' END) AS 事假, count(CASE 出勤状态 WHEN '迟到' THEN '○' END) AS 迟到, count(CASE 出勤状态 WHEN '早退' THEN '○' END) AS 早退, count(CASE 出勤状态 WHEN '旷工' THEN '○' END) AS 旷工, count(CASE 出勤状态 WHEN '其他' THEN '○' END) AS 其他 FROM dbo.人事_考勤表 where 部门='设备工具科' group by 日期,部门 order by 日期 desc