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

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;

文章插图
（3）最后要做的就是删除空值，消除间隙。只需要按照编号 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

文章插图
3.对结果集进行逆转置（列转行）
情景：将第一个情景中的结果集转换为多行。

文章插图
转换为

文章插图
解决方案：需要一个透视表，然后使用笛卡尔积。
需要事先知道转换为行的行数，就是列数。生成一个该行数的透视表，然后进行关联。再使用 case 表达式选择其中一列。
这里生成透视表使用递归生成，也可以从员工表查询去重部门编号的结果集作为透视表。
with recursive t3 as(select 1 as idunion allselect id+1 as id from t3where id < 3)select id*10 as DeptNo,case idwhen 1 then DeptNo_10when 2 then DeptNo_20when 3 then DeptNo_30end as Count from t3join deptcounts a ;

文章插图
4.将结果集逆转置为一列
将查询返回的所有列都放在一列中，并返回它们。
情景：返回10号部门所有员工的名字、角色和薪水，并将这三个值放在一列中。并在员工之间添加一行。如下：

文章插图
解决方案：由结果可以看出，每个员工需要返回四行，由此我们需要一张包含四行数据的透视表（使用 CTE）进行笛卡尔积。然后使用 case 表达式将三列转换为一列。
with recursive t4 as(select 1 as idunion allselect id +1 as id from t4where id < 4)/* select t4.id,a.EName,a.SAL,a.Role fromtest.emps ajoin t4where a.DeptNo = 10order by Ename ;*/selectcase t4.idwhen 1 then ENamewhen 2 then Rolewhen 3 then SALwhen 4 then ''end as EMPS fromtest.emps ajoin t4where a.DeptNo = 10order by Ename5.消除结果集中的重复值
在制作报表时，出现多行的同一列的值相同，需要这个列值只显示一次。
情景：从员工表返回部门编号和员工名字并按部门编号分组，对于每个部门编号只需显示一次。如下：

文章插图
解决方案：使用窗函数 Lag over 返回当前数据前一行的部门编号，并与当前数据的部门编号进行比较。如果相同就显示空值，即与前一行数据属于同一部门；如果不同就显示当前数据的部门编号，即当前数据是下一个部门数据的第一条数据。
SELECT casewhenlag(DeptNo) over(order by DeptNo)= DeptNo then '' else DeptNoend as DeptNo,EName FROM test.emps;6.转置结果集以简化涉及多行的计算
要执行的计算涉及多行的数据，为简化工作，你想要将这些行转置为列，这样你需要的所有数据都会出现在同一行中。
情景：薪水总额最高的部门是10号，如图一。想要计算20号部门和30号部门的薪水总额分别比10号部门少多少。最终结果如图二：