转置即旋转数据表的横纵方向，常用来改变数据布局，以便用新的角度观察。有些转置算法比较简单，比如行转列、列转行、双向转置；有些算法变化较多，比如动态转置、转置时跨行计算、关联转置等。这些转置算法对日常工作多有助益，值得我们学习讨论。
基础转置
行转列和列转行是最简单的转置算法，形式上互为逆运算，具体请看下面的问题及分析过程：
1.行转列：将销量分组表的quater字段里的值（行）Q1-Q4，转为新字段名（列）Q1-Q4，如下：
| year | quarter | amount | ----> | year | Q1 | Q2 | Q3 | Q4 | |
| year2018 | Q1 | 89 | year2018 | 89 | 93 | 88 | 99 | ||
| year2018 | Q2 | 93 | year2019 | 92 | 97 | 90 | 88 | ||
| year2018 | Q3 | 88 | |||||||
| year2018 | Q4 | 99 | |||||||
| year2019 | Q1 | 92 | |||||||
| year2019 | Q2 | 97 | ， | ||||||
| year2019 | Q3 | 90 | |||||||
| year2019 | Q4 | 88 |
2.列转行：将销量交叉表的字段名Q1-Q4，转为新字段quarter里的值Q1-Q4，如下：
| Year | Q1 | Q2 | Q3 | Q4 | ----> | year | quarter | amount | |
| year2018 | 89 | 93 | 88 | 99 | year2018 | Q1 | 89 | ||
| year2019 | 92 | 97 | 90 | 88 | year2018 | Q2 | 93 | ||
| year2018 | Q3 | 88 | |||||||
| year2018 | Q4 | 99 | |||||||
| year2019 | Q1 | 92 | |||||||
| year2019 | Q2 | 97 | |||||||
| year2019 | Q3 | 90 | |||||||
| year2019 | Q4 | 88 |
早期SQL的解决方案
对于行转列，早期的SQL没有pivot之类的专用函数（MySQL、HSQLDB等数据库现在也没有），这种情况下只能用多个基本函数的组合来实现行转列，同一个问题往往有多种实现方法。
方法1：case when子查询+分组汇总
|
/*mysql*/ Select year, max(Q1) 'Q1', max(Q2) 'Q2', max (Q3) 'Q3', max (Q4) 'Q4' from ( select year, case when quarter = 'Q1' then amount end Q1, case when quarter = 'Q2' then amount end Q2, case when quarter = 'Q3' then amount end Q3, case when quarter = 'Q4' then amount end Q4 from zz11 ) t group by year; |
方法2：sum if+分组汇总：
|
/*mysql*/ SELECT year, MAX(IF(quarter = 'Q1', amount, null)) AS 'Q1', MAX (IF(quarter = 'Q2', amount, null)) AS 'Q2', MAX (IF(quarter = 'Q3', amount, null)) AS 'Q3', MAX (IF(quarter = 'Q4', amount, null)) AS 'Q4' FROM zz11 GROUP BY year; |
其他方法还有 WITH ROLLUP+分组汇总或UNION+分组汇总等，这里不一一列举。这些方法表面各异，但本质其实都差不多，都是用分组的方法算出year的值，用枚举的方法依次生成新列Q1-Q4，同时用汇总的方式生成新列的值。
可以看到，即使最基础最简单的转置，早期SQL的代码也很长。原因在于，每个新列都要枚举出来，新列越多，代码就越长。如果新列是12个月、各州各省，可以想象SQL会更长。
只要新列是已知的，用笨办法总能枚举出来，所以新列多只会影响代码长度，并不会影响难度。如果新列是未知的，想枚举就困难多了。比如：大客户名单经常变动，需要将动态的大客名单由行转为列。遇到这种情况，SQL就很难解决了，通常要求助存储过程JAVA等语言工具，代码难度和维护难度都会陡然提升。
上面SQL其实还有个毛病：汇总算法难以理解。原表每年每季度只有一条数据，所以原本是不必汇总的，但因为计算year列需要分组，而SQL规定分组的同时必须汇总，所以必须对每年每季度的一条数据进行难以理解的汇总。因为这里的汇总毫无意义，所以汇总算法可以随便选，并不影响计算结果，比如将max换成sum。
SQL之所以规定分组的同时必须汇总，是因为集合化不彻底的缘故。具体来说，SQL只能表达多条记录组成的小集合，而没有语法或符号表达多个小集合组成的大集合，一旦遇到后者，比如分组的情况，就必须立刻汇总，让每个小集合变成一条记录，从而转变成前者。
列转行不涉及难以理解的汇总，早期SQL的思路相对简单，只需按列名依次取出Q1-Q4的记录，再用union拼起来就行，具体写法如下：
|
select year, 'Q1' quarter , Q1 as amount from zz111 union select year, 'Q2' quarter , Q2 as amount from zz111 union select year, 'Q3' quarter , Q3 as amount from zz111 union select year, 'Q4' quarter , Q4 as amount from zz111 |
列转行虽然思路简单，但因为要枚举组内新行，比如季度、月份、省份，所以代码依然会很长。值得庆幸的是，组内新行来自原表列名（字段名），而原表列名通常固定，所以一般不存在动态计算的情况，算法也不会太复杂。
引入pivot/unpivot函数
早期SQL实现转置确实不够方便，所以数据库厂商近几年推出专用函数，试图让用户更方便地实现转置。
用pivot实现行转列：
|
/*oracle*/ select * from zz111 pivot( max(amount) for quarter in( 'Q1'as Q1,'Q2' as Q2,'Q3' as Q3,'Q4' as Q4 ) ) |
仔细观察就会发现，pivot的确让代码缩短了不少，但并没解决本质问题，早期SQL存在的那些问题，现在一个都不少。
首先，pivot不能解决动态语法问题，所以遇到动态新列，依然要依靠存储过程/JAVA，开发难度和维护难度依然很大。
其次，pivot不能解决SQL集合问题，依然要用汇总去解决和汇总毫无关系的问题。对新手来说，这是难以理解的知识点，恳请留意。
在某些特殊情况下，汇总也是有意义的，比如销量分组表另有一个字段customer，使每年每季度的数据有多条，在这种情况下需要行转列，并计算每年每季度amount最大的值。如下：
| customer | year | quarter | amount | year | Q1 | Q2 | Q3 | Q4 | ||
| companyA | year2018 | Q1 | 89 | ----> | year2018 | 89 | 93 | 88 | 100 | |
| companyB | year2018 | Q1 | 100 | year2019 | 92 | 97 | 90 | 88 | ||
| companyA | year2018 | Q2 | 93 | |||||||
| companyB | year2018 | Q3 | 88 | |||||||
| companyC | year2018 | Q4 | 99 | |||||||
| companyD | year2019 | Q1 | 92 | |||||||
| companyE | year2019 | Q2 | 97 | |||||||
| companyF | year2019 | Q3 | 90 | |||||||
| companyG | year2019 | Q4 | 88 |
在这种特殊情况下，使用汇总才是真正合理的，合理到核心代码都不用改：
|
/*oracle*/ select * from (select year,quarter,amount from zz111) pivot( max(amount) for quarter in( 'Q1'as Q1,'Q2' as Q2,'Q3' as Q3,'Q4' as Q4 ) ) |
可以看到，上述特殊情况实际上不是字面意义上的“行转列”，而是“分组汇总后再行转列”。也许有些初学者会有疑问：这明明是两种不同的算法，为何会使用相同的核心代码？但读过前文的人就会明白，这是SQL集合化不彻底的缘故。
相对而言，列转行函数unpivot就好理解多了：
|
select * from zz111 unpivot( amount for quarter in( Q1,Q2,Q3,Q4 ) ) |
可以看到，unpivot不仅可以解决代码冗长的问题，而且由于不涉及汇总，所以理解起来也很容易。另外列转行很少遇到动态取列名的需求，因此基础算法不会发生太复杂的变化。可以这样说，unpivot是个相对成功的函数。
双向转置
双向转置可以理解为行列互换或镜像，通常来说，交叉表的双向转置才有意义。
3．将年度-季度销售表转置为季度-年度销售表，即将year的值转为新列名year2018、year2019，同时将列名Q1-Q4转为新列quarter的值。
如下所示：
| Year | Q1 | Q2 | Q3 | Q4 | ----> | quarter | year2018 | year2019 |
| year2018 | 89 | 93 | 88 | 99 | Q1 | 89 | 92 | |
| year2019 | 92 | 97 | 90 | 88 | Q2 | 93 | 97 | |
| Q3 | 88 | 90 | ||||||
| Q4 | 99 | 88 |
双向转置的实现思路就写在名字里，即先对Q1-Q4执行列转行，再对year2018、year2019执行行转列。如果用小型数据库实现，代码会是下面这样：
|
/*mysql*/ select quarter, max(IF(year = 'year2018', amount, null)) AS 'year2018', max(IF(year = 'year2019', amount, null)) AS 'year2019' from ( select year, 'Q1' quarter , Q1 as amount from crosstb union select year, 'Q2' quarter , Q2 as amount from crosstb union select year, 'Q3' quarter , Q3 as amount from crosstb union select year, 'Q4' quarter , Q4 as amount from crosstb ) t group by quarter |
上述代码包含了行转列和列转行两种算法，所以也兼具了两者的缺点，比如代码冗长、不支持动态行、汇总算法难理解。这里需要注意的是，JAVAC++等过程性语言擅长多步骤计算，代码的复杂度和代码长度可视为线性关系，SQL则不同，很难分步骤分模块或断点调试，这就导致SQL的复杂度随代码长度呈指数增长。总之，你会发现双向转置要比它表面看起来更难实现。
上面的算法是先列转行再行转列，理论上似乎也可以反过来，即先行转列再列转行，但实际上并非如此。如果先行转列，就会导致子查询的数量从1个增加到4个（由union导致），不仅代码更长，而且性能更差。当然，如果用支持with 语句的Oracle等数据库，反过来就没问题。
事实上，如果不是小型数据库，而是Oracle或MSSQL，那直接pivot、unpivot联用就可以了，用不到with语句。代码如下：
|
/*Oracle*/ select * from( select * from crosstb unpivot( amount for quarter in( Q1,Q2,Q3,Q4 ) ) ) pivot( max(amount) for year in( 'year2018' as year2018,'year2019' as year2019 ) ) order by quarter |
上述代码思路更清晰，但由于子查询难以调试难以按独立步骤运行，所以理解起来并不会太轻松。另外列转行的顺序是不可控的，为了让quarter列按Q1-Q4的固定顺序排列，最后必须用order by排序。可以想象，如果需要自定义顺序（比如0、a、1），则需要造假表并关联该假表，难度会大幅提升。
Pivot/unpivot其实还有个共同的问题，也请初学者注意：这类函数并非ANSI规范，所以各厂商语法区别较大，迁移时比较困难。
动态转置
前面简单提到过动态转置，这里再具体解释一下：由于待转置的值不是固定的，而是会动态增减的，所以转换后的行或列也不是固定的，而是要动态计算，这种算法就是动态转置。
4.动态行转列：部门-地区平均工资表中的地区会随着业务拓展而增加，请将地区字段的值（行）转为新字段名（列）。
如下图：
| Dept | Area | AvgSalary | ----> | Dept | Beijing | Shanghai | ... |
| Sales | Beijing | 3100 | Sales | 3100 | 2700 | ||
| Marketing | Beijing | 3300 | Marketing | 3300 | 2400 | ||
| HR | Beijing | 3200 | HR | 3200 | 2900 | ||
| Sales | Shanghai | 2700 | |||||
| Marketing | Shanghai | 2400 | |||||
| HR | Shanghai | 2900 | |||||
| … |
乍一看，这个问题应该可以用pivot解决，只须在in里用子查询动态取得地区的唯一值，比如：
|
/*Oracle 11*/ select * from temp pivot ( max(AvgSalary) for Area in( select distinct Area from temp ) ) |
上述语句看上去很合理，但实际上，pivot里的in函数和一般的in函数不同，一般的in函数里的确可以用子查询，但pivot的in函数不能直接支持子查询。
要想直接支持子查询，必须使用古怪的xml关键字，即：
|
/*Oracle 11*/ select * from temp pivot xml( max(AvgSalary) for Area in( select distinct Area from temp ) ) |
这样就会产生一个古怪的中间结果集，该结果集含有2个字段，其中一个字段的类型是XML，如下所示。
| Dept | Area_XML |
| HR | <PivotSet><item><column name = "AREA">Beijing</column><column name = "MAX(AVGSALARY)">3200</column></item><item><column name = "AREA">Shanghai</column><column name = "MAX(AVGSALARY)">3200</column></item></PivotSet> |
| Marketing | <PivotSet><item><column name = "AREA">Beijing</column><column name = "MAX(AVGSALARY)">3300</column></item><item><column name = "AREA">Shanghai</column><column name = "MAX(AVGSALARY)">2400</column></item></PivotSet> |
| Sales | <PivotSet><item><column name = "AREA">Beijing</column><column name = "MAX(AVGSALARY)">3100</column></item><item><column name = "AREA">Shanghai</column><column name = "MAX(AVGSALARY)">2700</column></item></PivotSet> |
对于上述中间结果集，还需要动态解析XML，获得AREA的节点，并动态生成表结构，再动态填入数据，才能算出我们的目标。只用SQL已经无法实现此类动态算法，后续代码必须用JAVA或存储过程嵌入SQL才行，最终代码很长，放在文中影响阅读，这里就不贴了。
5.组内记录行转列：收入来源表中，逻辑上Name是分组字段， Source和Income是组内字段，每个Name对应多条组内记录，数量不固定，现在要将组内记录由行转列。
如下所示：
| Name | Source | Income | ----> | Category | Source1 | Income1 | Source2 | Income2 |
| David | Salary | 8000 | David | Salary | 8000 | Bonus | 15000 | |
| David | Bonus | 15000 | Daniel | Salary | 9000 | |||
| Daniel | Salary | 9000 | Andrew | Shares | 26000 | Sales | 23000 | |
| Andrew | Shares | 26000 | Robert | Bonus | 13000 | |||
| Andrew | Sales | 23000 | ||||||
| Robert | Bonus | 13000 |
本算法的整体思路很清晰：先生成结果表的表结构，再向结果表插入数据，最后输出结果表。
思路虽然清晰，但实际代码非常繁琐，这是因为代码中大量涉及动态语法，包括嵌套循环中的动态语法，而SQL本身不支持动态语法。为了弥补SQL的缺陷，只能用其他语言配合SQL，比如JAVA或存储过程。这些语言不擅长结构化计算，却非要实现结构化算法，代码必然冗长。有兴趣的可按如下步骤实现 ：
1. 计算出结果表应该有几组组内字段（colN），即对源表按Name分组，求各组记录数，进而求最大的记录数。上表中David和Andrew的记录数最多，有2条，所以colN=2。通过colN，很容易计算出动态列的列名colNames。
2. 动态生成建结果表的SQL字符串（cStr）。难点在于循环colN次，每次都要生成一组组内字段，所有字段包括1个固定列+2*colN个动态列（如图）。
3. 动态执行上述字符串，生成临时表。代码形如：execute immediate cStr;
4. 计算结果表应该插入的关键字列表（rowKeys），即对源表按Name去重。上表中rowKeys=["David","Daniel","Andrew","Robert"]
5. 循环rowKeys，每次动态生成向结果表插入记录的SQL字符串iStr，并动态执行。生成iStr时，先根据当前Name查询源表，以获得对应的记录列表，这里要动态生成SQL并动态执行。接下来循环该记录列表，拼凑出iStr并执行，从而完成一次循环。
6. 查询结果表，返回数据。
可以想象，如果SQL支持动态语法，或者JAVA/存储过程内置结构化函数库（脱离SQL），那实际的代码就会精简很多 。
还应该注意到，上面第4步的算法是对Name去重，去重相当于分组后求分组字段的值，而第1步的算法是分组后求各组记录数。这两步同时有分组的动作，理论上是可以复用的，但由于SQL的集合化不彻底，分组的同时必须强制汇总，所以无法复用分组的结果。如果数据量小，能否复用并不重要，最多就是代码丑不丑的问题，而一旦遇到数据量较大或多处复用的算法，能否复用就决定性能高低了。
6. 复杂静态行列转置:每人每天在考勤表有7条固定记录，需要将其转置为2条，其中第1条的In、Out、Break、Return字段值分别对应原表的第1、7、2、3条的Time字段值，第2条对应原表的1、7、5、6的Time字段值。
如下所示：
原表
| Per_Code | in_out | Date | Time | Type |
| 1110263 | 1 | 2013-10-11 | 09:17:14 | In |
| 1110263 | 6 | 2013-10-11 | 11:37:00 | Break |
| 1110263 | 5 | 2013-10-11 | 11:38:21 | Return |
| 1110263 | 0 | 2013-10-11 | 11:43:21 | NULL |
| 1110263 | 6 | 2013-10-11 | 13:21:30 | Break |
| 1110263 | 5 | 2013-10-11 | 14:25:58 | Return |
| 1110263 | 2 | 2013-10-11 | 18:28:55 | Out |
转置后目标表
| Per_Code | Date | In | Out | Break | Return |
| 1110263 | 2013-10-11 | 09:17:14 | 18:28:55 | 11:37:00 | 11:38:21 |
| 1110263 | 2013-10-11 | 09:17:14 | 18:28:55 | 13:21:30 | 14:25:58 |
由于转置后列数固定，无须动态算法，因此可用SQL实现本算法，具体如下：
|
With r as( select Per_code,Date,Time,row_number() over(partition by Per_Code,Date order by Time) rn from temp) select Per_code,Date, max(case when rn=1 then Time end) In, max(case when rn=7 then Time end) Out, max(case when rn=2 then Time end) Break, max(case when rn=3 then Time end) Return from r group by Per_code,Date union select Per_code,Date, max(case when rn=1 then Time end) In, max(case when rn=7 then Time end) Out, max(case when rn=5 then Time end) Break, max(case when rn=6 then Time end) Return from r group by Per_code,Date |
SQL集合无序，所以不能用序号引用记录，而本算法又需要序号，所以我们不得不人为制造一个序号，即上述代码中的with子句。有了序号之后，取数据就方便多了。至于明明没有汇总算法，却硬要max，这是SQL集合化不彻底的缘故，前面已经解释过这种现象。
7复杂动态行列转置：用户表和记录表通过用户ID关联，表示用户在2018年某日存在一条活动记录。现在需要计算出2018年的每周，各用户是否存在活动记录，用户名需转置为列。
如下所示：
源表结构
| User | Record | |
| ID(pk) | 1:N----> | ID(pk) |
| Name | Date(pk) |
转置后目标表的数据
| Week | User1 | User2 | User3 |
| 1 | Yes | No | Yes |
| 2 | Yes | Yes | No |
| 3 | Yes | No | Yes |
| 4 | No | Yes | Yes |
由于列是动态的，所以只能用存储过程/JAVA+动态SQL的方法实现，代码很长，这里照例不贴，下面只讲思路。
要实现上述算法，需要先进行准备工作：将用户表和记录表关联起来；新加计算列，算出Date字段值相对于2018-01-01的周数，最大不应超过53；对周数求最大值，可获得目标表的关键字列表rowKeys；对关联表去重，计算出目标表新增的列名colNames。
接下来是动态转置算法：用colNames动态生成建目标表的SQL，再动态执行SQL；循环rowKeys，每次循环时先从关联表取数据，再动态生成Insert SQL，再动态执行SQL。
上述动态转置算法前面也见到过，事实上，凡此类涉及动态列的转置，都有个动态生成目标表结构，再动态插入数据的过程。这个算法难度较大，这既是SQL缺乏动态语言能力的表现，也是我们不得不求助于JAVA/存储过程的根本原因。后面遇到类似的情况，我会用“动态转置”直接带过。
转置同时列间计算
前面都是单纯的转置，作为习题比较合适，在实际工作中，转置的同时通常会附带其他计算，比如列之间的计算。
8表Temp存储2014年每个客户每个月的应付款情况，现在要将其转置，客户名为主键（关键字）列，1-12月为转置列，对应的值为当月应付款金额，如果当月无数据，则用上月的应付款金额。
如下所示：
源表
| ID | Name | amount_payable | due_date |
| 112101 | CA | 12800 | 2014-02-21 |
| 112102 | CA | 3500 | 2014-06-15 |
| 112104 | LA | 25000 | 2014-01-12 |
| 112105 | LA | 20000 | 2014-11-15 |
| 112106 | LA | 8000 | 2014-12-06 |
转置后目标表
| name | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| CA | 12800 | 12800 | 12800 | 12800 | 3500 | 3500 | 3500 | 3500 | 3500 | 3500 | 3500 | |
| LA | 25000 | 25000 | 25000 | 25000 | 25000 | 25000 | 25000 | 25000 | 25000 | 25000 | 20000 | 8000 |
因为转置后的列是固定的，所以可以用SQL解决，大致思路是：造一个包含单字段month，值为1-12的临时表t1；通过源表的日期算出月份，字段名也是month；用这两个表进行左关联，造出连续的应付款记录，注意这里有很多数据无效；使用pivot实现行转列，用min汇总去除无效数据。具体SQL如下：
|
With t2 as(select name,amount_payable,EXTRACT(MONTH from dule_date) month from temp ) ,t1 as(SELECT rownum month FROM dual CONNECT BY LEVEL <= 12 ) ,t3 as(select t2.name,t2.amount_payable,t1.month from t1 left join t2 on t1.month>=t2.month ) select * from t3 pivot(min(amount_payable) for month in(1 as "1",2 as "2",3 as "3",4 as "4",5 as "5",6 as "6",7 as "7",8 as "8",9 as "9",10 as "10",11 as "11",12 as "12")) |
上述SQL不长，但是很难理解，尤其是造无效数据这一古怪算法。之所以出现这种情况，是因为SQL集合本身没有序号，也不擅长有序计算，尤其是行间计算，只能采取一些古怪的手段去间接实现。
表间关联列转行
9子表动态插入主表：订单表和订单明细是主子关系，一条订单对应至少一条明细，现在要将明细动态插入订单，如下所示：
源表关系
| Order | ----> | OrderDetail |
| ID(pk) | OrderID(PK) | |
| Customer | Number(pk) | |
| Date | Product | |
| Amount |
转置后目标表
| ID | Customer | Date | Product1 | Amount1 | Product2 | Amount2 | Product3 | Amount3 |
| 1 | 3 | 2019-01-01 | Apple | 5 | Milk | 3 | Salt | 1 |
| 2 | 5 | 2019-01-02 | Beef | 2 | Pork | 4 | ||
| 3 | 2 | 2019-01-02 | Pizza | 3 |
由于列是动态的，所以只能用存储过程/JAVA+动态SQL的方法实现，大致思路是：先把两表关联起来；对关联表（或子表）按ID分组，求各组记录数，求最大值，从而算出目标表的动态列列表colNames；对关联表（或主表）的ID去重，算出目标表的主键列表rowKeys；根据colNames和rowKeys实现动态转置算法。
10多表关联列转行： 考试成绩表Exam和补考成绩Retest表都是Students的子表，现在需要将两个子表转置到主表的列，且增加一个总分，注意考试的科目不定，且并非每个人都会补考，但考试的科目一定包含了补考科目。
源表数据及关系
| Exam table | <----1:N | Students table | 1:N ----> | Retest table | ||||||
| stu_id | subject | score | stu_id | stu_name | class_id | stu_id | subject | score | ||
| 1 | Chinese | 80 | 1 | Ashley | 301 | 2 | Chinese | 78 | ||
| 1 | Math | 77 | 2 | Rachel | 301 | 3 | Math | 82 | ||
| 2 | Chinese | 58 | 3 | Emily | 301 | |||||
| 2 | Math | 67 | ||||||||
| 3 | Chinese | 85 | ||||||||
| 3 | Math | 56 | ||||||||
转置后目标表
| stu_id | stu_name | Chinese_score | Math_score | total_score | Chinese_retest | Math_retest |
| 1 | Ashley | 80 | 77 | 156 | ||
| 2 | Rachel | 58 | 67 | 125 | 78 | |
| 3 | Emily | 85 | 56 | 141 | 82 |
如果科目固定，就可以用SQL解决，先将Students和Exam左关联并piovt，然后Retest和Exam左关联并pivot，最后再左关联一次。
但每次考试的科目不固定，因此目标表的列是动态的，只能用存储过程/JAVA+动态SQL的方法实现，大致思路是：先将2表左关联至Students；对关联表按stu_id分组，求各组记录数，再求最大记录数，从而计算出目标表的动态列列表colNames；对关联表按stu_id去重，计算出目标表的主键列表rowKeys；根据colNames和rowKeys实现动态转置算法。
分栏
11源表记录各大洲的部分城市人口，现在要分别找出欧洲和非洲的城市和人口，分两栏横向转置，注意目标列是固定的，但源表行数是动态的。如下所示：
| Continent | City | Population | ----> | EuropeCity | EuropePopulation | AfricaCity | EuropePopulation |
| Africa | Cairo | 6789479 | Moscow | 8389200 | Cairo | 6789479 | |
| Africa | Kinshasa | 5064000 | London | 7285000 | Kinshasa | 5064000 | |
| Africa | Alexandria | 3328196 | Alexandria | 3328196 | |||
| Europe | Moscow | 8389200 | |||||
| Europe | London | 7285000 |
目标表的结构是固定的，可以用SQL解决，思路是：过滤出包含欧洲城市的记录，用rownum算出行号，作为计算列；类似地，过滤出包含非洲城市的记录；将两者进行full join，并取出所需字段。
具体SQL如下：
|
With t1 as(select city Europecity,population Europepopulation,rownum rn from temp where continent='Europe') ,t2 as(select city Africacity,population Africapopulation,rownum rn from temp where continent='Africa') select t1.Europecity,t1.Europepopulation,t2.Africacity,t2.Africapopulation from t1 full join t2 on t1.rn=t2.rn |
总结
通过上面的讨论可以发现，只有最简单的三种转置可以直接用piovt/unpivot实现，且仅限大型数据库，还需注意xml解析、结果集乱序，以及难以移植的问题。
对于有一定难度的转置算法来说，如果列是固定的，通常就能用SQL解决，但代码通常很难写，需要熟知SQL的缺陷，并掌握各类古怪的技巧来弥补这些缺陷。前面遇到的缺陷包括：集合化不彻底、集合无序号、不擅长有序计算、难以分步计算、难以调试代码等。
如果列是动态的，复杂程度将大幅上升，只能用JAVA/存储过程，代码将非常繁琐。事实上，不支持动态结构，也是SQL的重大缺陷。
SQL的上述缺陷是个独特的历史现象，在其它计算机语言中并不存在，比如VBC++JAVA，甚至包括存储过程。当然，这些语言的集合计算能力比较弱，缺乏结构化计算类库，需要编写大量代码才能实现上述算法（指不嵌入SQL的情况）。
采用esProc 的 SPL能更好地适应这些问题。esProc 是专业的数据计算引擎，基于有序集合设计，像SQL一样提供了完善的结构化函数，又和Java等语言类似天然支持分步计算，相当于 Java 和 SQL 优势的结合。使用SPL （替代Java）来配合SQL可以轻松解决上面的问题：
1行转列，有类似的pivot函数
| A | |
| 1 | =connect("orcl").query@x("select * from T") |
| 2 | =A1.pivot(year; quarter, amount) |
2列转行，有相当于unpivot的函数
| A | |
| 1 | =connect("orcl").query@x("select year,Q1,Q2,Q3,Q4 from T") |
| 2 | =A1.pivot@r(year; quarter, amount) |
3双向转置，结合使用pivot及其逆
| A | |
| 1 | =connect("orcl").query@x("select year,Q1,Q2,Q3,Q4 from T") |
| 2 | =A1.pivot@r(year;quarter,amount).pivot(quarter;year,amount) |
4动态行转列，SPL的pivot可以支持动态数据结构
| A | |
| 1 | =connect("orcl").query@x("select Dept,Area,AvgSalary from T") |
| 2 | =A1.pivot@r(year;quarter,amount).pivot(Dept; Area, AvgSalary) |
5组内记录行转列，分步计算并支持动态数据结构
| A | B | |
| 1 | =orcl.query("select Name,Source,Income from T") | |
| 2 | =gData=A1.group(Name) | |
| 3 | =colN=gData.max(~.len()) | |
| 4 | =create(Name, ${colN.("Source"+string(~)+", Income"+string(~)).concat@c()}) | |
| 5 | for gData | =A5. Name | A5.conj([Source, Income]) |
| 6 | >A4.record(B5) | |
6复杂静态行列转置，天然支持序号
| A | B | |
| 1 | =connect("orcl").query@x("select * from DailyTime order by Per_Code,Date,Time") | =A1.group((#-1)7) |
| 2 | =create(Per_Code,Date,In,Out,Break,Return) | =B1.(~([1,7,2,3,1,7,5,6])) |
| 3 | =B2.conj([~.Per_Code,~.Date]|~.(Time).m([1,2,3,4])|[~.Per_Code,~.Date]|~.(Time).m([5,6,7,8])) | >A2.record(A3) |
7复杂动态行列转置
| A | B | |
| 1 |
=connect("db").query("select t1.ID as ID, t1.Name as Name, t2.Date as Date from User t1, Record t2 where t1.ID=t2.ID") |
|
| 2 | =A1.derive(interval@w("2018-01-01",Date)+1:Week) | =A2.max(Week) |
| 3 | =A2.group(ID) | =B2.new(~:Week,${A3.(""No":"+Name).concat@c()}) |
| 4 | =A3.run(~.run(B3(Week).field(A3.#+1,"Yes"))) | |
8转置同时列间计算
| A | B | |
| 1 | =orcl.query@x("select name,amount_payable from T") | |
| 2 | =create(name,${12.string@d()}) | =A1.group(customID) |
| 3 | for B2 | =12.(null) |
| 4 | >A3.run(B3(month(due_date))= amount_payable) | |
| 5 | >B3.run(~=ifn(~,~[-1])) | |
| 6 | =A2.record(B2.name|B3) | |
9子表动态插入主表
| A | B | |
| 1 | =orcl.query@x("select * from OrderDetail left join Order on Order.ID=OrderDetail.OrderID") | |
| 2 | =A1.group(ID) | =A2.max(~.count()).("Product"+string(~)+","+"Amount"+string(~)).concat@c() |
| 3 | =create(ID,Customer,Date,${B2}) | >A2.run(A3.record([ID,Customer,Date]|~.([Product,Amount]).conj())) |
10多表关联列转行
| A | B | |
| 1 |
=orcl.query@x("select t1.stu_id stu_id,t1.stu_name stu_name,t2.subject subject,t2.score score1,t3.score score2 from Students t1 left join Exam t2 on t1.stu_id=t2.stu_id left join Retest t3 on t1.stu_id=t3.stu_id and t2.subject=t3.subject order by t1.stu_id,t2.subject |
|
| 2 | =A1.group(stu_id) | =A1.group(subject) |
| 3 | =create(stu_id,stu_name,${(B2.(~.subject+"_score")|"total_score"|B2.(~.subject+"_retest ")).string()}) | |
| 4 | >A2.run(A3.record([stu_id,stu_name]|B2.(~(A2.#).score1)|A2.sum(score1)|B2.(~(A2.#).score2))) | |
11分栏
| A | B | |
| 1 | =orcl.query@x("select * from World where Continent in('Europe','Africa')") | |
| 2 | =A1.select(Continent:"Europe") | =A1.select(Continent:"Africa") |
| 3 | =create('Europe City',Population,'Africa City', Population) | =A3.paste(A2.(City),A2.(Population),B2.(City),B2.(Population)) |