Oracle行轉列操作

標籤：

 有時候我們在展示表中資料的時候，需要將行轉為列來顯示，如以下形式：

原表結構展示如下：
---------------------------
產品名稱    銷售額     季度
---------------------------
乳酪          50     第一季度
乳酪          60     第二季度
啤酒          50     第二季度
啤酒          80     第四季度
---------------------------

現在需要將上面的原表結構轉換為如下所示的結構形式來展示：
--------------------------------------------------------------------------
產品名稱   第一季度銷售額   第二季度銷售額   第三季度銷售額   第四季度銷售額
--------------------------------------------------------------------------
乳酪　　　　　　50　　　　　　　 60               0              　　0
啤酒　　　　　　0　　　　　　　　50               0              　　80
--------------------------------------------------------------------------

一、建立銷售表sale_hst表結構

--建立銷售表create table sale_hst(    prdt_name varchar2(10),--產品名稱    sale_amt number(8),--銷售額    season varchar2(10)--季度);

二、插入基礎資料

--插入如上所示的基礎資料insert into sale_hst values (‘乳酪‘,50,‘第一季度‘);insert into sale_hst values (‘乳酪‘,60,‘第二季度‘);insert into sale_hst values (‘啤酒‘,50,‘第二季度‘);insert into sale_hst values (‘啤酒‘,80,‘第四季度‘);

三、使用SQL語句轉換

方案1：使用case...when...then...else...end...語句

--方案1：使用case...when...then...else...end...語句select    prdt_name,    sum(case when season=‘第一季度‘ then sale_amt else 0 end) 第一季度銷售額,    sum(case when season=‘第二季度‘ then sale_amt else 0 end) 第二季度銷售額,    sum(case when season=‘第三季度‘ then sale_amt else 0 end) 第三季度銷售額,    sum(case when season=‘第四季度‘ then sale_amt else 0 end) 第四季度銷售額from sale_hstgroup by prdt_name;

方案2：Oracle下可以用decode函數處理

說明：

Oracle下可以用decode函數處理:
 decode函數是Oracle PL/SQL中功能強大的函數之一，目前還只有Oracle公司的SQL提供了此函數，其他資料庫廠商的SQL實現還沒有此功能。

 decode函數功能如下:
 decode(欄位或欄位的運算，值1，值2，值3）
 這個函數啟動並執行結果是，當欄位或欄位的運算的值等於值1時，該函數傳回值2，否則傳回值3
 當然值1，值2，值3也可以是運算式，這個函數使得某些sql語句簡單了許多。

 

--方案2：Oracle下可以用decode函數處理select    prdt_name,    sum(decode(season,‘第一季度‘,sale_amt,0)) as 第一季度銷售額,    sum(decode(season,‘第二季度‘,sale_amt,0)) as 第二季度銷售額,    sum(decode(season,‘第三季度‘,sale_amt,0)) as 第三季度銷售額,    sum(decode(season,‘第四季度‘,sale_amt,0)) as 第四季度銷售額from sale_hstgroup by prdt_name;

 

 

 

 有時候我們又有如下的需求：

原表的資料形式展示如下：

shopping表:
----------------------------------
u_id       goods            num
----------------------------------
1           蘋果               2
2           梨子               5
1           西瓜               4
3           葡萄               1
3           香蕉               1
1           橘子               3
----------------------------------

轉換為如下的形式1展示：

--------------------------------------------
u_id          goods_sum　　　　total_num
--------------------------------------------
1             蘋果,西瓜,橘子　　　　　　9
2             梨子　　　　　　　　　　　5
3             葡萄,香蕉　　　　　　　　 2
--------------------------------------------

轉換為如下的形式2展示:
------------------------------------------------------
u_id          goods_sum　　　　　　　　　　total_num
------------------------------------------------------
1             蘋果(2斤),西瓜(4斤),橘子(3斤)　　　　9
2             梨子(5斤)　　　　　　　　　　　　　　5
3             葡萄(1斤),香蕉(1斤)　　　　　　　　　2
------------------------------------------------------

一、建立購物表shopping表結構

--建立購物表shoppingcreate table shopping(    u_id number(10),    goods varchar2(8),    num number(10)   );

 

二、插入基礎資料

--插入如上所示的基礎資料insert into shopping values (1,‘蘋果‘,2);insert into shopping values (2,‘梨子‘,5);insert into shopping values (1,‘西瓜‘,4);insert into shopping values (3,‘葡萄‘,1);insert into shopping values (3,‘香蕉‘,1);insert into shopping values (1,‘橘子‘,3);

 

三、使用SQL語句轉換

形式1：

--形式1的語句select u_id, wmsys.wm_concat(goods) goods_sum,sum(num) total_num  from shopping   group by u_id;

 形式2：

--形式2的語句select u_id, wmsys.wm_concat(goods || ‘(‘ || num || ‘斤)‘ ) goods_sum,sum(num) total_num  from shopping  group by u_id;

說明：

Oracle中wm_concat(column)函數的使用：
wmsys使用者的wm_concate函數
Oracle資料庫中，使用wm_concat(column)函數，可以進列欄位合并，Oracle中的wmsys.wm_concat主要實現行轉列功能(說白了就是將查詢的某一列值使用逗號進行隔開拼接，成為一條資料)。wmsys.wm_concat除了單獨使用外還可以和over函數結合使用。

Oracle行轉列操作