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))