转载:https://www.cnblogs.com/cnjavahome/p/4225467.html
有时候我们在展示表中数据的时候,需要将行转为列来显示,如以下形式:
原表结构展示如下:
---------------------------
产品名称 销售额 季度
---------------------------
奶酪 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_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 第一季度销售额,
sum(decode(season,'第二季度',sale_amt,0)) as 第二季度销售额,
sum(decode(season,'第三季度',sale_amt,0)) as 第三季度销售额,
sum(decode(season,'第四季度',sale_amt,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函数结合使用。