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 (‘啤酒‘,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_hst
group 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 第一季度销售额,‘第二季度‘,0)) as 第二季度销售额,‘第三季度‘,0)) as 第三季度销售额,‘第四季度‘,0)) as 第四季度销售额
from sale_hst
group 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表结构

--创建购物表shopping
create 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函数结合使用。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!