SPL 查询与报表计算实战指南 - 7 各种行列转换和逆转换
7 各种行列转换和逆转换
这类任务经常在报表输出时出现，难点在于转换后的列名是动态未知的。大部分SQL转置时必须写出列名，通常要改用存储过程动态生成列名，再动态执行SQL，结构很复杂。
例1：保持部门分组不变，将州由行值转为列名
数据源：各部门在各州的薪金汇总表。
目标：保持部门不变，将州由行转列，其中，州的名字和数量随员工的入职离职而变化，事先未知。
SPL代码：
A |
|
1 |
$select Dept,State,sum(Salary) Amount from Employee.txt group by Dept,State |
2 |
=A1.pivot(Dept;State,Amount) |
A1：加载数据。
A2：保持部门不变，将州由行转列。函数pivot用于转置。
l 知识点：转置pivot
SPL有个pivot函数可用于转置和逆转置，不必写出列名。
参数g：原表的分组字段名/表达式。
参数F：原表的明细字段名，其字段值将转为新列名。
参数V：原表的明细字段名，其字段值将转为F的新列值。
例2：（逆转置）保持部门分组不变，将州由列名转为行值。
数据源：定时生成的交叉表，左表头是部门，上表头是州。
目标：保持部门不变，将州由列转行，其中，州的名字和数量事先未知
SPL代码：
A |
|
1 |
$select Dept,State,sum(Salary) Amount from Employee.txt group by Dept,State |
2 |
=A1.pivot(Dept;State,Amount) |
3 |
|
4 |
=A2.pivot@r(Dept;State,Amount) |
A1-A2模拟数据源。
A3：保持部门不变，将州由列逆转为行。函数pivot@r可实现逆转置。
l 知识点：逆转置pivot@r
参数g：原表的分组字段名/表达式。
参数F：新表的明细字段名，其字段值由原表分组字段之外的所有字段名转换而来。
参数V：新表的明细字段名，其字段值由原表F的字段值转换而来。
例3：将客户、合同状态、时间形成的交叉表旋转九十度
数据源：客户合同状态表，左表头是客户，上表头是合同的签订、交付、回款三种状态，交叉格是状态发生的日期。
目标：将交叉表顺时针旋转九十度，将原左表头的客户转到上表头，原上表头的状态改到交叉格，原交叉格的日期改到左表头并按正序排序。
SPL代码：
A |
|
1 |
$select * from 7\3.txt |
2 |
=A1.pivot@r(Client;State,Date) |
3 |
=A2.select(Date) |
4 |
=A3.pivot(Date;Client,State) |
A1：加载数据。
A2：保持客户不变，将状态由列转行。
A3：过滤掉空状态。
A4：保持日期不变，将客户由行转列。
例4：保持部门分组不变，将州和薪水由纵向排列转为横向展开。
数据源：三部门在各州的薪水汇总表。
目标：保持部门不变，将州和薪水这两列由行转列。其中，州的名字和数量事先未知
SPL代码：
A |
|
1 |
$select Dept,State,sum(Salary) Amount from Employee.txt where Dept in ('Finance','Marketing','HR') group by Dept,State |
2 |
=A1.groupc(Dept;State,Amount) |
A1：加载数据。
A2：保持部门不变，将州和薪水这两列由行转列，自动生成新字段名。函数groupc可实现多列转置。
l 知识点：多列转置groupc
参数g：原表的分组字段名/表达式。
参数F,F2…Fn：原表的多列/多个明细字段，其字段值将按照字段顺序依次横向分布在新表中。
新字段名将按照序号自动生成，形如_2_3…，也可以定义字段名。
A2也可以手工指定字段名：
=A1.groupc(Dept;State,Amount;S1,A1,S2,A2,S3,A3,S4,A4)
计算结果：
上面的字段名也可以用动态代码生成：
A |
|
6 |
=A1.group(Dept).max(~.len()).conj(["S"/~,"A"/~]).concat@c() |
7 |
=A1.groupc(Dept;State,Amount;${A6}) |
扩展阅读
(https://c.raqsoft.com.cn/article/1741576289020)
(https://c.raqsoft.com.cn/article/1740572868946)
(https://c.raqsoft.com.cn/article/1737365746895)
(https://c.raqsoft.com.cn/article/1736815793573)
(https://c.raqsoft.com.cn/article/1733145439627)
