SQL 转置计算

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

基础转置

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

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

上述代码包含了行转列和列转行两种算法，所以也兼具了两者的缺点，比如代码冗长、不支持动态行、汇总算法难理解。这里需要注意的是，JAVA\C++等过程性语言擅长多步骤计算，代码的复杂度和代码长度可视为线性关系，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的上述缺陷是个独特的历史现象，在其它计算机语言中并不存在，比如VB\C++\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))