转置即旋转数据表的横纵方向，常用来改变数据布局，以便用新的角度观察。有些转置算法比较简单，比如行转列、列转行、双向转置；有些算法变化较多，比如动态转置、转置时跨行计算、关联转置等。这些转置算法对日常工作多有助益，值得我们学习讨论。

基础转置

行转列和列转行是最简单的转置算法，形式上互为逆运算，具体请看下面的问题及分析过程：

1.行转列：将销量分组表的quater字段里的值（行）Q1-Q4，转为新字段名（列）Q1-Q4，如下：

2.列转行：将销量交叉表的字段名Q1-Q4，转为新字段quarter里的值Q1-Q4，如下：

早期SQL的解决方案

对于行转列，早期的SQL没有pivot之类的专用函数（MySQL、HSQLDB等数据库现在也没有），这种情况下只能用多个基本函数的组合来实现行转列，同一个问题往往有多种实现方法。

方法1：case when子查询+分组汇总

方法2：sum if+分组汇总：

其他方法还有 WITH ROLLUP+分组汇总或UNION+分组汇总等，这里不一一列举。这些方法表面各异，但本质其实都差不多，都是用分组的方法算出year的值，用枚举的方法依次生成新列Q1-Q4，同时用汇总的方式生成新列的值。

可以看到，即使最基础最简单的转置，早期SQL的代码也很长。原因在于，每个新列都要枚举出来，新列越多，代码就越长。如果新列是12个月、各州各省，可以想象SQL会更长。

只要新列是已知的，用笨办法总能枚举出来，所以新列多只会影响代码长度，并不会影响难度。如果新列是未知的，想枚举就困难多了。比如：大客户名单经常变动，需要将动态的大客名单由行转为列。遇到这种情况，SQL就很难解决了，通常要求助存储过程\JAVA等语言工具，代码难度和维护难度都会陡然提升。

上面SQL其实还有个毛病：汇总算法难以理解。原表每年每季度只有一条数据，所以原本是不必汇总的，但因为计算year列需要分组，而SQL规定分组的同时必须汇总，所以必须对每年每季度的一条数据进行难以理解的汇总。因为这里的汇总毫无意义，所以汇总算法可以随便选，并不影响计算结果，比如将max换成sum。

SQL之所以规定分组的同时必须汇总，是因为集合化不彻底的缘故。具体来说，SQL只能表达多条记录组成的小集合，而没有语法或符号表达多个小集合组成的大集合，一旦遇到后者，比如分组的情况，就必须立刻汇总，让每个小集合变成一条记录，从而转变成前者。

列转行不涉及难以理解的汇总，早期SQL的思路相对简单，只需按列名依次取出Q1-Q4的记录，再用union拼起来就行，具体写法如下：

        列转行虽然思路简单，但因为要枚举组内新行，比如季度、月份、省份，所以代码依然会很长。值得庆幸的是，组内新行来自原表列名（字段名），而原表列名通常固定，所以一般不存在动态计算的情况，算法也不会太复杂。

引入pivot/unpivot函数

         早期SQL实现转置确实不够方便，所以数据库厂商近几年推出专用函数，试图让用户更方便地实现转置。

用pivot实现行转列：

仔细观察就会发现，pivot的确让代码缩短了不少，但并没解决本质问题，早期SQL存在的那些问题，现在一个都不少。

首先，pivot不能解决动态语法问题，所以遇到动态新列，依然要依靠存储过程/JAVA，开发难度和维护难度依然很大。

其次，pivot不能解决SQL集合问题，依然要用汇总去解决和汇总毫无关系的问题。对新手来说，这是难以理解的知识点，恳请留意。

在某些特殊情况下，汇总也是有意义的，比如销量分组表另有一个字段customer，使每年每季度的数据有多条，在这种情况下需要行转列，并计算每年每季度amount最大的值。如下：

在这种特殊情况下，使用汇总才是真正合理的，合理到核心代码都不用改：

可以看到，上述特殊情况实际上不是字面意义上的“行转列”，而是“分组汇总后再行转列”。也许有些初学者会有疑问：这明明是两种不同的算法，为何会使用相同的核心代码？但读过前文的人就会明白，这是SQL集合化不彻底的缘故。

相对而言，列转行函数unpivot就好理解多了：

         可以看到，unpivot不仅可以解决代码冗长的问题，而且由于不涉及汇总，所以理解起来也很容易。另外列转行很少遇到动态取列名的需求，因此基础算法不会发生太复杂的变化。可以这样说，unpivot是个相对成功的函数。

双向转置

双向转置可以理解为行列互换或镜像，通常来说，交叉表的双向转置才有意义。

3．将年度-季度销售表转置为季度-年度销售表，即将year的值转为新列名year2018、year2019，同时将列名Q1-Q4转为新列quarter的值。

如下所示：

        双向转置的实现思路就写在名字里，即先对Q1-Q4执行列转行，再对year2018、year2019执行行转列。如果用小型数据库实现，代码会是下面这样：

上述代码包含了行转列和列转行两种算法，所以也兼具了两者的缺点，比如代码冗长、不支持动态行、汇总算法难理解。这里需要注意的是，JAVA\C++等过程性语言擅长多步骤计算，代码的复杂度和代码长度可视为线性关系，SQL则不同，很难分步骤分模块或断点调试，这就导致SQL的复杂度随代码长度呈指数增长。总之，你会发现双向转置要比它表面看起来更难实现。

上面的算法是先列转行再行转列，理论上似乎也可以反过来，即先行转列再列转行，但实际上并非如此。如果先行转列，就会导致子查询的数量从1个增加到4个（由union导致），不仅代码更长，而且性能更差。当然，如果用支持with 语句的Oracle等数据库，反过来就没问题。

事实上，如果不是小型数据库，而是Oracle或MSSQL，那直接pivot、unpivot联用就可以了，用不到with语句。代码如下：

上述代码思路更清晰，但由于子查询难以调试难以按独立步骤运行，所以理解起来并不会太轻松。另外列转行的顺序是不可控的，为了让quarter列按Q1-Q4的固定顺序排列，最后必须用order by排序。可以想象，如果需要自定义顺序（比如0、a、1），则需要造假表并关联该假表，难度会大幅提升。

Pivot/unpivot其实还有个共同的问题，也请初学者注意：这类函数并非ANSI规范，所以各厂商语法区别较大，迁移时比较困难。

动态转置    

前面简单提到过动态转置，这里再具体解释一下：由于待转置的值不是固定的，而是会动态增减的，所以转换后的行或列也不是固定的，而是要动态计算，这种算法就是动态转置。

4.动态行转列：部门-地区平均工资表中的地区会随着业务拓展而增加，请将地区字段的值（行）转为新字段名（列）。

如下图：

乍一看，这个问题应该可以用pivot解决，只须在in里用子查询动态取得地区的唯一值，比如：

上述语句看上去很合理，但实际上，pivot里的in函数和一般的in函数不同，一般的in函数里的确可以用子查询，但pivot的in函数不能直接支持子查询。

要想直接支持子查询，必须使用古怪的xml关键字，即：

这样就会产生一个古怪的中间结果集，该结果集含有2个字段，其中一个字段的类型是XML，如下所示。

对于上述中间结果集，还需要动态解析XML，获得AREA的节点，并动态生成表结构，再动态填入数据，才能算出我们的目标。只用SQL已经无法实现此类动态算法，后续代码必须用JAVA或存储过程嵌入SQL才行，最终代码很长，放在文中影响阅读，这里就不贴了。

5.组内记录行转列：收入来源表中，逻辑上Name是分组字段， Source和Income是组内字段，每个Name对应多条组内记录，数量不固定，现在要将组内记录由行转列。

如下所示：

本算法的整体思路很清晰：先生成结果表的表结构，再向结果表插入数据，最后输出结果表。

思路虽然清晰，但实际代码非常繁琐，这是因为代码中大量涉及动态语法，包括嵌套循环中的动态语法，而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字段值。

如下所示：

原表

转置后目标表

由于转置后列数固定，无须动态算法，因此可用SQL实现本算法，具体如下：

SQL集合无序，所以不能用序号引用记录，而本算法又需要序号，所以我们不得不人为制造一个序号，即上述代码中的with子句。有了序号之后，取数据就方便多了。至于明明没有汇总算法，却硬要max，这是SQL集合化不彻底的缘故，前面已经解释过这种现象。

7复杂动态行列转置：用户表和记录表通过用户ID关联，表示用户在2018年某日存在一条活动记录。现在需要计算出2018年的每周，各用户是否存在活动记录，用户名需转置为列。

如下所示：

源表结构

 转置后目标表的数据

由于列是动态的，所以只能用存储过程/JAVA+动态SQL的方法实现，代码很长，这里照例不贴，下面只讲思路。

要实现上述算法，需要先进行准备工作：将用户表和记录表关联起来；新加计算列，算出Date字段值相对于2018-01-01的周数，最大不应超过53；对周数求最大值，可获得目标表的关键字列表rowKeys；对关联表去重，计算出目标表新增的列名colNames。

接下来是动态转置算法：用colNames动态生成建目标表的SQL，再动态执行SQL；循环rowKeys，每次循环时先从关联表取数据，再动态生成Insert SQL，再动态执行SQL。

上述动态转置算法前面也见到过，事实上，凡此类涉及动态列的转置，都有个动态生成目标表结构，再动态插入数据的过程。这个算法难度较大，这既是SQL缺乏动态语言能力的表现，也是我们不得不求助于JAVA/存储过程的根本原因。后面遇到类似的情况，我会用“动态转置”直接带过。

转置同时列间计算

前面都是单纯的转置，作为习题比较合适，在实际工作中，转置的同时通常会附带其他计算，比如列之间的计算。

8表Temp存储2014年每个客户每个月的应付款情况，现在要将其转置，客户名为主键（关键字）列，1-12月为转置列，对应的值为当月应付款金额，如果当月无数据，则用上月的应付款金额。

如下所示：

源表

转置后目标表

因为转置后的列是固定的，所以可以用SQL解决，大致思路是：造一个包含单字段month，值为1-12的临时表t1；通过源表的日期算出月份，字段名也是month；用这两个表进行左关联，造出连续的应付款记录，注意这里有很多数据无效；使用pivot实现行转列，用min汇总去除无效数据。具体SQL如下：

上述SQL不长，但是很难理解，尤其是造无效数据这一古怪算法。之所以出现这种情况，是因为SQL集合本身没有序号，也不擅长有序计算，尤其是行间计算，只能采取一些古怪的手段去间接实现。

表间关联列转行

9子表动态插入主表：订单表和订单明细是主子关系，一条订单对应至少一条明细，现在要将明细动态插入订单，如下所示：

源表关系

转置后目标表

由于列是动态的，所以只能用存储过程/JAVA+动态SQL的方法实现，大致思路是：先把两表关联起来；对关联表（或子表）按ID分组，求各组记录数，求最大值，从而算出目标表的动态列列表colNames；对关联表（或主表）的ID去重，算出目标表的主键列表rowKeys；根据colNames和rowKeys实现动态转置算法。

10多表关联列转行： 考试成绩表Exam和补考成绩Retest表都是Students的子表，现在需要将两个子表转置到主表的列，且增加一个总分，注意考试的科目不定，且并非每个人都会补考，但考试的科目一定包含了补考科目。

源表数据及关系

转置后目标表

如果科目固定，就可以用SQL解决，先将Students和Exam左关联并piovt，然后Retest和Exam左关联并pivot，最后再左关联一次。

但每次考试的科目不固定，因此目标表的列是动态的，只能用存储过程/JAVA+动态SQL的方法实现，大致思路是：先将2表左关联至Students；对关联表按stu_id分组，求各组记录数，再求最大记录数，从而计算出目标表的动态列列表colNames；对关联表按stu_id去重，计算出目标表的主键列表rowKeys；根据colNames和rowKeys实现动态转置算法。

分栏

11源表记录各大洲的部分城市人口，现在要分别找出欧洲和非洲的城市和人口，分两栏横向转置，注意目标列是固定的，但源表行数是动态的。如下所示：

目标表的结构是固定的，可以用SQL解决，思路是：过滤出包含欧洲城市的记录，用rownum算出行号，作为计算列；类似地，过滤出包含非洲城市的记录；将两者进行full join，并取出所需字段。

具体SQL如下：

总结

通过上面的讨论可以发现，只有最简单的三种转置可以直接用piovt/unpivot实现，且仅限大型数据库，还需注意xml解析、结果集乱序，以及难以移植的问题。

对于有一定难度的转置算法来说，如果列是固定的，通常就能用SQL解决，但代码通常很难写，需要熟知SQL的缺陷，并掌握各类古怪的技巧来弥补这些缺陷。前面遇到的缺陷包括：集合化不彻底、集合无序号、不擅长有序计算、难以分步计算、难以调试代码等。

如果列是动态的，复杂程度将大幅上升，只能用JAVA/存储过程，代码将非常繁琐。事实上，不支持动态结构，也是SQL的重大缺陷。

SQL的上述缺陷是个独特的历史现象，在其它计算机语言中并不存在，比如VB\C++\JAVA，甚至包括存储过程。当然，这些语言的集合计算能力比较弱，缺乏结构化计算类库，需要编写大量代码才能实现上述算法（指不嵌入SQL的情况）。

采用esProc 的 SPL能更好地适应这些问题。esProc 是专业的数据计算引擎，基于有序集合设计，像SQL一样提供了完善的结构化函数，又和Java等语言类似天然支持分步计算，相当于 Java 和 SQL 优势的结合。使用SPL （替代Java）来配合SQL可以轻松解决上面的问题：

1行转列，有类似的pivot函数

2列转行，有相当于unpivot的函数

3双向转置，结合使用pivot及其逆

4动态行转列，SPL的pivot可以支持动态数据结构

5组内记录行转列，分步计算并支持动态数据结构

6复杂静态行列转置，天然支持序号

7复杂动态行列转置

8转置同时列间计算

9子表动态插入主表

10多表关联列转行

11分栏

SQL 转置计算