SQL 报表制作和整形 _生活百科

本文章主要介绍制作报表的查询，这些查询通常需要考虑与报表相关的格式设置，还需使用多级聚合。
1.将结果集转置为一行（行转列）
将多行中的值转换为单行中的列。
情景：有一个员工表，统计出一个结果集，显示了每个部门的员工数量，如下图一。现在需要调整输出格式，显示成一行，如图二。

文章插图
图一

文章插图
图二
解决方案：使用 case 表达式和 SUM 聚合函数来转置结果集。
使用 case 表达式将行拆分成列，并且标记每行数据是否属于这个部门。然后，由于这里的问题是计算每个部门的员工数量，因此使用 SUM 聚合函数。
selectsum(case DeptNo when 10 then 1 else 0 end) as DeptNo_10,sum(case DeptNo when 20 then 1 else 0 end)as DeptNo_20,sum(case DeptNo when 30 then 1 else 0 end) as DeptNo_30 from test.emps;
拆解：
?。?）第一步将行转换为列，并标记每行数据属于哪个部门。

文章插图
?。?）第二步是计算每个部门的人数，根据DeptNo分组。第二步属于过渡步骤，熟练了可以直接跳过。

文章插图
?。?）第三步，目标是返回一行数据，所以删除 DeptNo 和 group by 即可。

文章插图
也可以使用另一种写法，先分组统计出每个部门的人数，然后再行转列。
selectsum(case DeptNo when 10 then cnt else 0 end )as DeptNo_10 ,sum(case DeptNo when 20 then cnt else 0 end )as DeptNo_20 ,sum(case DeptNo when 30 then cnt else 0 end )as DeptNo_30 from ( select DeptNo,count(*) cnt fromtest.emps group by DeptNo) a ;

文章插图
2.将结果集转置为多行
通过为给定列中每个不同的值都创建一列，也是行转列。不同的是要输出多行。
情景：图一是每个员工及其角色。想让每个角色为一列，每列下面为是该角色的员工名称，如图二。

文章插图

文章插图
图一图二
解决方案：
该情景不同于上一个情景，这次需要返回多行，所以不能按照角色分组然后使用聚合函数。要解决这个问题，必须让每个角色/员工名组合是独一无二。可以使用窗函数 row_number() over(partition by 角色 order by 员工名) 给每个组合做编号。然后再使用 case 表达式和聚合函数 Max 对结果进行转置，最后根据窗函数做的编号进行分组。
SELECTmax(case Role when 'PPS' then EName else '' end) as PPS,Max(case Role when 'PM' then EName else '' end)as PM,max(case Role when 'BD' then EName else '' end) as BD,max(case Role when 'CS' then EName else '' end) as CSfrom ( selectRole,EName, row_number() over(partition by Role order by EName) rnFROM test.emps) a group by rn
拆解：
?。?）图一是按照上个解决方案查询出的结果。虽然给每个角色显示了每一列，也返回了多行，但是中间存在间隙。所以不能直接转置，需要先给每个角色/员工名组合做编号，如图二。

文章插图

文章插图
图一图二
?。?）现在根据上述结果集进行转置。
SELECT rn,case Role when 'PPS' then EName else '' end as PPS,case Role when 'PM' then EName else '' endas PM,case Role when 'BD' then EName else '' end as BD,case Role when 'CS' then EName else '' end as CSfrom ( selectRole,EName, row_number() over(partition by Role order by EName) rnFROM test.emps) a;

文章插图
?。?）最后要做的就是删除空值，消除间隙。只需要按照编号 rn 分组然后使用 MAX 聚合函数即可解决。
SELECT rn,max(case Role when 'PPS' then EName else '' end) as PPS,Max(case Role when 'PM' then EName else '' end)as PM,max(case Role when 'BD' then EName else '' end) as BD,max(case Role when 'CS' then EName else '' end) as CSfrom ( selectRole,EName, row_number() over(partition by Role order by EName) rnFROM test.emps) a group by rn