SQL行列转置



需求：一个考勤数据表，现需要按季度提取对应月份考勤数据。
考勤数据如下：

1. SELECT F_52677 AS 年,F_52678 AS 月,F_52679 AS 胸卡号,F_52680 AS 应到,F_52681 AS 缺卡次数 FROM T_753 WHERE 1=1

复制代码

结果：

需要把月的结果行转置到列，10月对应首月，11月对应次月，12月对应尾月，转换后SQL：

1. SELECT F_52679 AS 胸卡号,sum(case when F_52678 = '10' then F_52680 end) AS 首月,sum(case when F_52678 = '10' then F_52681 end) AS 首月缺卡,sum(case when F_52678 = '11' then F_52680 end) AS 次月,sum(case when F_52678 = '11' then F_52681 end) AS 次月缺卡,sum(case when F_52678 = '12' then F_52680 end) AS 尾月,sum(case when F_52678 = '12' then F_52681 end) AS 尾月缺卡 FROM T_753  WHERE 1=1 group by F_52679

复制代码

结果：

SQL行列转置



需求：一个考勤数据表，现需要按季度提取对应月份考勤数据。
考勤数据如下：

1. SELECT F_52677 AS 年,F_52678 AS 月,F_52679 AS 胸卡号,F_52680 AS 应到,F_52681 AS 缺卡次数 FROM T_753 WHERE 1=1

复制代码

结果：

需要把月的结果行转置到列，10月对应首月，11月对应次月，12月对应尾月，转换后SQL：

1. SELECT F_52679 AS 胸卡号,sum(case when F_52678 = '10' then F_52680 end) AS 首月,sum(case when F_52678 = '10' then F_52681 end) AS 首月缺卡,sum(case when F_52678 = '11' then F_52680 end) AS 次月,sum(case when F_52678 = '11' then F_52681 end) AS 次月缺卡,sum(case when F_52678 = '12' then F_52680 end) AS 尾月,sum(case when F_52678 = '12' then F_52681 end) AS 尾月缺卡 FROM T_753  WHERE 1=1 group by F_52679

复制代码

结果：