Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4

行列轉換問題

在製作報表或查詢資料的時候，我們經常可能會遇到的一個問題是行列換。概念很簡單，行轉列就是將一行中指定的N個欄位轉為N條記錄。反過來便是列轉行了。

一、行轉列

有如下幾個部門，分別對應了若干個員工：

select dname, e.ename

from scott.emp e, scott.dept d

where e.deptno = d.deptno

資料如下：

DNAME	ENAME
RESEARCH	SMITH
SALES	ALLEN
SALES	WARD
RESEARCH	JONES
SALES	MARTIN
SALES	BLAKE
ACCOUNTING	CLARK
RESEARCH	SCOTT
ACCOUNTING	KING
SALES	TURNER
RESEARCH	ADAMS
SALES	JAMES
RESEARCH	FORD
ACCOUNTING	MILLER

現在要求按一個部門一個欄位列出所有員工，如下進行顯示：

RN	ACCOUNTING	RESEARCH	SALES
1	CLARK	JONES	WARD
2	KING	FORD	TURNER
3	MILLER	ADAMS	ALLEN
4		SMITH	JAMES
5		SCOTT	BLAKE
6			MARTIN

應該如何實現？

首先，我們取出每個部門的名稱（或其他唯一性的編號），然後對每一條記錄進行識別。當遇到部門名稱為“accounting”時，即顯示該名稱，否則不顯示。其他依次類推。於是，就有如下語句：

select dname,

decode(d.dname, 'ACCOUNTING', e.ename) accounting,

decode(d.dname, 'RESEARCH', e.ename) RESEARCH,

decode(d.dname, 'SALES', e.ename) SALES

from scott.emp e, scott.dept d

where e.deptno = d.deptno

order by dname

結果如下：

DNAME	ACCOUNTING	RESEARCH	SALES
ACCOUNTING	CLARK
ACCOUNTING	KING
ACCOUNTING	MILLER
RESEARCH		SCOTT
RESEARCH		ADAMS
RESEARCH		FORD
RESEARCH		JONES
RESEARCH		SMITH
SALES			JAMES
SALES			TURNER
SALES			BLAKE
SALES			MARTIN
SALES			ALLEN
SALES			WARD

可以從結果中看出，每當遇到一條符合"accounting"的記錄時，他對應的其他兩個部門的欄位就變為空了，那麼，該如何將這些空白補足，並按照最大行數顯示呢？比如，accounting部門是3條，research是5條，sales是6條，結果就要按照6行來顯示。

這時，我們就要藉助row_number這個分析函式。首先給每個部門的員工按1-N進行編號，編號

依據任意：

select dname,

row_number() over(partition by dname order by dname) rn,

decode(d.dname, 'ACCOUNTING', e.ename) accounting,

decode(d.dname, 'RESEARCH', e.ename) RESEARCH,

decode(d.dname, 'SALES', e.ename) SALES

from scott.emp e, scott.dept d

where e.deptno = d.deptno

於是我們得到了這樣的資料：

DNAME	RN	ACCOUNTING	RESEARCH	SALES
ACCOUNTING	1	CLARK
ACCOUNTING	2	KING
ACCOUNTING	3	MILLER
RESEARCH	1		JONES
RESEARCH	2		FORD
RESEARCH	3		ADAMS
RESEARCH	4		SMITH
RESEARCH	5		SCOTT
SALES	1			WARD
SALES	2			TURNER
SALES	3			ALLEN
SALES	4			JAMES
SALES	5			BLAKE
SALES	6			MARTIN

可能以上資料還不是很明顯，但是基本上可以看出個端倪。後面就是合併的過程了。由於資料有一個很明顯的特徵，就是每個相同的編號組對應只有一個員工，其他都為空，因此按編號合併的時候，每個編號也只有一個員工。

按照這樣的思路，只要將編號相同的記錄合併到一起即可。這個合併，即可以採用wmsys.wm_concat函式，也可以使用max這樣的函式。以下就是實現的語句：

select rn,

max(accounting) accounting,

max(RESEARCH) RESEARCH,

max(SALES) SALES

from (select dname,

row_number() over(partition by dname order by dname) rn,

decode(d.dname, 'ACCOUNTING', e.ename) accounting,

decode(d.dname, 'RESEARCH', e.ename) RESEARCH,

decode(d.dname, 'SALES', e.ename) SALES

from scott.emp e, scott.dept d

where e.deptno = d.deptno)

group by rn

order by rn

下面資料就是我們想要的結果：

RN	ACCOUNTING	RESEARCH	SALES
1	CLARK	JONES	WARD
2	KING	FORD	TURNER
3	MILLER	ADAMS	ALLEN
4		SMITH	JAMES
5		SCOTT	BLAKE
6			MARTIN

二、列轉行

列轉行的思路有兩種：

第一種是按欄位拆分成若干部分，然後再union all。比如說，拆分成三個部分，然後再union all。

第二種是構造跟所需列數相同的行。比如說，有3個列要轉成行，則構造一個三行的虛表。

假設有一隻基金，該基金的投資組成有股票、債券、現金、權證、其他這幾個型別，其中每個型別對應的值為當前市值，都對應一個欄位。如下表：

基金程式碼	股票	債券	權證	其他
200001	5	3	7	12
200002	13	5	6	2

問，如何顯示成如下形式的資料：

基金程式碼	投資型別	投資市值
200001	股票	5
200001	債券	3
200001	權證	7
200001	其他	8
200002	股票	13
200002	債券	5
200002	權證	6
200002	其他	5

以第一種方式實現，就是簡單的union all合併：

select fp.fcode 基金程式碼, '股票' 投資型別, fp.stock 投資市值

from fundportfolio fp

union all

select fp.fcode 基金程式碼, '債券' 投資型別, fp.bonds 投資市值

from fundportfolio fp

union all

select fp.fcode 基金程式碼, '權證' 投資型別, fp.warnt 投資市值

from fundportfolio fp

union all

select fp.fcode 基金程式碼, '其他' 投資型別, fp.other 投資市值

from fundportfolio fp

這種寫法的缺點是如果型別較多，就會很多基本大同小異的SQL union all起來，程式碼上顯得很拖沓。那如何寫出一個簡潔的呢？答案就是用第二種方法，構造和轉換列數相同的行數的虛擬表，然後兩表做完全笛卡爾積。假設原表有N行，需要轉換的欄位有M個，即每行要衍生出M行，所以結果集就有N×M行，這個就是構造這個虛擬表的原理。下面語句即實現了這個原理：

select fp.fcode 基金程式碼,

decode(r, 1, '股票', 2, '債券', 3, '權證', 4, '其他') 投資型別,

decode(r, 1, fp.stock, 2, fp.bonds, 3, fp.warnt, 4, fp.other) 投資是指

from fundportfolio fp, (select rownum r from dual connect by rownum <= 4)

即實現了所要求的資料。

另外，這個用法還有一個衍生的用途。

假設有資料：

begin	end
1	3
2	5

現要生成如下的資料：

begin	end	sn
1	3	1
1	3	2
1	3	3
2	5	2
2	5	3
2	5	4
2	5	5

是不是跟上面的需求很像？原理上，就是一行擴充套件成多行，但是跟前面的情況不一樣的地方是，前面擴充套件的行數是固定的，而這裡要擴充套件的行數是動態的，其擴充套件依據是由begin和end之間的差值決定的。那如何獲取這個動態行數或者說控制結果按照所需的行數就是一個關鍵的問題。

首先，按照上面第二種方法實現。在這裡簡要的說就是最大數過濾法。首先，取出begin和end之間差值最大的數值N，然後構造一個N+1行的表，表內有一個欄位s，序號為1..N+1，再與原表做笛卡爾積，最後按照s<=end過濾多餘的行。SQL如下：

with tmp as(

select 1 begin, 3 end from dual union all

select 2, 5 from dual)

select begin, end, begin + r - 1

from tmp, (select rownum r from (select max(end - begin)+1 n from tmp) connect by rownum <= n)

where r <= end

order by 1,3

行列轉換問題總結

一、行轉列

二、列轉行

相關文章