oracle行转列与列转行的几种方式汇总

Hadara ·

更新时间:2024-09-20

· 420 次阅读

1、准备数据：REST表

2、查询数据

3、行转列

方式1：使用 case when then方式

方式2：使用 decode函数

方式3：使用pivot函数

4、列转行

5、直接使用unpivot函数 --列转行

总结

1、准备数据：REST表

-- 创建表REST
CREATE TABLE REST (
  "ID" NUMBER,
  "AMOUNT" NUMBER(19,0),
  "MONTH" VARCHAR2(255 BYTE)
);
--执行添加数据语句
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '100', 'Jan');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '100', 'Feb');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '66', 'Mar');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '77', 'Jun');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '88', 'Dec');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '12', 'Aug');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '22', 'Feb');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '33', 'Apr');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '232', 'Jul');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '43', 'Sep');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '544', 'Oct');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '65', 'Nov');

2、查询数据

3、行转列 方式1：使用 case when then方式

case 条件

when 值1 then 返回值1

when 值2 then 返回值2

..........

else 默认值

end

-- 使用case when 方式
SELECT
id,
sum( CASE month WHEN 'Jan' THEN amount ELSE 0 END ) AS Jan_amount,
sum( CASE month WHEN 'Feb' THEN amount ELSE 0 END ) AS Feb_amount,
sum( CASE month WHEN 'Mar' THEN amount ELSE 0 END ) AS Mar_amount,
sum( CASE month WHEN 'Apr' THEN amount ELSE 0 END ) AS Apr_amount,
sum( CASE month WHEN 'May' THEN amount ELSE 0 END ) AS May_amount,
sum( CASE month WHEN 'Jun' THEN amount ELSE 0 END ) AS Jun_amount,
sum( CASE month WHEN 'Jul' THEN amount ELSE 0 END ) AS Jul_amount,
sum( CASE month WHEN 'Aug' THEN amount ELSE 0 END ) AS Aug_amount,
sum( CASE month WHEN 'Sep' THEN amount ELSE 0 END ) AS Sep_amount,
sum( CASE month WHEN 'Oct' THEN amount ELSE 0 END ) AS Oct_amount,
sum( CASE month WHEN 'Nov' THEN amount ELSE 0 END ) AS Nov_amount,
sum( CASE month WHEN 'Dec' THEN amount ELSE 0 END ) AS Dec_amount 
FROM
REST 
GROUP BY
id

case when 另一种方式：

        case when 条件 = 值1 then 返回值1

        case when 条件 = 值1 then 返回值1

        else 默认值

        end

SELECT
id,
sum( CASE  WHEN month ='Jan' THEN amount ELSE 0 END ) AS Jan_amount,
sum( CASE  WHEN month = 'Feb' THEN amount ELSE 0 END ) AS Feb_amount,
sum( CASE  WHEN month = 'Mar' THEN amount ELSE 0 END ) AS Mar_amount,
sum( CASE  WHEN month = 'Apr' THEN amount ELSE 0 END ) AS Apr_amount,
sum( CASE  WHEN month = 'May' THEN amount ELSE 0 END ) AS May_amount,
sum( CASE  WHEN month = 'Jun' THEN amount ELSE 0 END ) AS Jun_amount,
sum( CASE  WHEN month = 'Jul' THEN amount ELSE 0 END ) AS Jul_amount,
sum( CASE  WHEN month = 'Aug' THEN amount ELSE 0 END ) AS Aug_amount,
sum( CASE  WHEN month = 'Sep' THEN amount ELSE 0 END ) AS Sep_amount,
sum( CASE  WHEN month = 'Oct' THEN amount ELSE 0 END ) AS Oct_amount,
sum( CASE  WHEN month = 'Nov' THEN amount ELSE 0 END ) AS Nov_amount,
sum( CASE  WHEN month = 'Dec' THEN amount ELSE 0 END ) AS Dec_amount 
FROM
REST 
GROUP BY
id

结果为：

方式2：使用 decode函数

decode函数： DECODE(条件, 值1, 返回值1, 值2,返回值2, 值3,返回值3, . . . else 缺省值)

含义：if 条件 = 值1 then 返回值1 elsif 条件 = 值2 then 返回值2 else (缺省值) endif

--使用decode函数
SELECT
id,
sum( decode( month, 'Jan', amount, 0 ) ) Jan_amount,
sum( decode( month, 'Feb', amount, 0 ) ) Feb_amount,
sum( decode( month, 'Mar', amount, 0 ) ) Mar_amount,
sum( decode( month, 'Apr', amount, 0 ) ) Apr_amount,
sum( decode( month, 'May', amount, 0 ) ) May_amount,
sum( decode( month, 'Jun', amount, 0 ) ) Jun_amount,
sum( decode( month, 'Jul', amount, 0 ) ) Jul_amount,
sum( decode( month, 'Aug', amount, 0 ) ) Aug_amount,
sum( decode( month, 'Sep', amount, 0 ) ) Sep_amount,
sum( decode( month, 'Oct', amount, 0 ) ) Oct_amount,
sum( decode( month, 'Nov', amount, 0 ) ) Nov_amount,
sum( decode( month, 'Dec', amount, 0 ) ) Dec_amount 
FROM
REST 
GROUP BY
id

结果和方式1一样

方式3：使用pivot函数

pivot
(
<聚合函数>(要聚合的列)
for <要转换的列> in (要转换的列值 as 要转换成的列名)
)

SELECT
* 
FROM
 REST pivot (
SUM(amount) FOR month IN (
'Jan' AS Jan_amount,
'Feb' AS Feb_amount,
'Mar' AS Mar_amount,
'Apr' AS Apr_amount,
'May' AS May_amount,
'Jun' AS Jun_amount,
'Jul' AS Jul_amount,
'Aug' AS Aug_amount,
'Sep' AS Sep_amount,
'Oct' AS Oct_amount,
'Nov' AS Nov_amount,
'Dec' AS Dec_amount 
) 
);

结果为：这个结果会发现，如果数据为空没有赋值为0

下面这个方法解决null 转为0 问题

SELECT
NVl(Jan_amount,0) Jan_amount,
NVl(Feb_amount,0) Feb_amount,
NVl(Mar_amount,0) Mar_amount,
NVl(Apr_amount,0) Apr_amount,
NVl(May_amount,0) May_amount,
NVl(Jun_amount,0) Jun_amount,
NVl(Jul_amount,0) Jul_amount,
NVl(Aug_amount,0) Aug_amount,
NVl(Sep_amount,0) Sep_amount,
NVl(Oct_amount,0) Oct_amount,
NVl(Nov_amount,0) Nov_amount,
NVl(Dec_amount,0) Dec_amount
FROM
 REST pivot (
SUM(amount) FOR month IN (
'Jan' AS Jan_amount,
'Feb' AS Feb_amount,
'Mar' AS Mar_amount,
'Apr' AS Apr_amount,
'May' AS May_amount,
'Jun' AS Jun_amount,
'Jul' AS Jul_amount,
'Aug' AS Aug_amount,
'Sep' AS Sep_amount,
'Oct' AS Oct_amount,
'Nov' AS Nov_amount,
'Dec' AS Dec_amount 
) 
);

结果和方式1一样：

4、列转行

在上述pivot 方法的原sql语句上再加上unpivot函数，将列再转为行，在unpivot函数中，amount：表示由列转换为行后的数据

month：表示由列转换为行后的列名

select * from REST
pivot (sum(amount) for month in (
'Jan' AS Jan_amount,
'Feb' AS Feb_amount,
'Mar' AS Mar_amount,
'Apr' AS Apr_amount,
'May' AS May_amount,
'Jun' AS Jun_amount,
'Jul' AS Jul_amount,
'Aug' AS Aug_amount,
'Sep' AS Sep_amount,
'Oct' AS Oct_amount,
'Nov' AS Nov_amount,
'Dec' AS Dec_amount 
))
unpivot ( amount for month in(Jan_amount,Feb_amount,Mar_amount,Apr_amount,May_amount,Jun_amount,Jul_amount,Aug_amount,Sep_amount,Oct_amount,Nov_amount,Dec_amount));

结果为：

5、直接使用unpivot函数 --列转行

准备数据：TEST表

CREATE TABLE TEST (
  "ID" NUMBER(12,0) NOT NULL,
  "JAN" VARCHAR2(255 BYTE),
  "FEB" VARCHAR2(255 BYTE),
  "MAR" VARCHAR2(255 BYTE),
  "APR" VARCHAR2(255 BYTE),
  "MAY" VARCHAR2(255 BYTE),
  "JUN" VARCHAR2(255 BYTE),
  "JUL" VARCHAR2(255 BYTE),
  "AUG" VARCHAR2(255 BYTE),
  "SEP" VARCHAR2(255 BYTE),
  "OCT" VARCHAR2(255 BYTE),
  "NOV" VARCHAR2(255 BYTE),
  "DEC" VARCHAR2(255 BYTE)
);
-- 插入数据
INSERT INTO "CERPAWCSADM"."TEST" VALUES ('1', '33', '2', '3', '4', '5', '6', '7', '8', '9', '99', '8', '6');
INSERT INTO "CERPAWCSADM"."TEST" VALUES ('2', '22', '3', '4', '6', '5', '7', '0', '7', '22', '21', '343', '76');
INSERT INTO "CERPAWCSADM"."TEST" VALUES ('3', '88', '3', '4', '5', '7', '9', '7', '2', '2', '231', '56', '78');

查询出的数据

列转行sql

SELECT
* 
FROM TEST
unpivot ( amount for month in(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC));

结果为：

总结

到此这篇关于oracle行转列与列转行的几种方式汇总的文章就介绍到这了,更多相关oracle行转列与列转行内容请搜索软件开发网以前的文章或继续浏览下面的相关文章希望大家以后多多支持软件开发网！

转行 Oracle

1024 个赞

需要登录后方可回复, 如果你还没有账号请注册新账号

Python File(文件) 方法

Emily 2020-05-26

734

.net生成验证码

Radinka 2020-06-02

725

Oracle的PDB数据库创建DIRECTORY时遇到ORA-65254问题及解决方法

Pascall 2023-07-20

1404

Oracle Users表空间重命名问题解决

Octavia 2023-07-20

1480

Oracle数据库安装教程并实现公网远程连接(内网穿透)

Dianthe 2023-07-20

221

Oracle数据库实现远程访问方法

Karima 2023-07-20

1744

Oracle压缩表空间的基本步骤

Nita 2023-07-20

1495

Oracle中实现类似于Mysql中的Field()函数功能

Calandra 2023-07-20

853

oracle分区表创建(自动按年、月、日分区)实战记录

Roselani 2023-07-20

1764

Oracle数据迁移MySQL的三种简单方法

Brigitte 2023-07-20

1330

Oracle Database 23c新特性之关联更新和删除示例详解

Tia 2023-07-20

862

oracle中sql%rowcount的作用详解

Isleta 2023-07-20

497

oracle行转列与列转行的几种方式汇总

Hadara 2023-07-20

420

Oracle一行拆分为多行方法实例

Leona 2023-07-20

132

Oracle常用函数超详细整理

Carmen 2023-07-20

1472

Oracle中时间日期转化函数to_date和to_char的具体使用

Dara 2023-07-20

1782

oracle中ORA-12514问题解决方法

Bonnie 2023-07-20

585

12类Oracle日期函数超全面总结

Tamara 2023-07-20

1893

oracle11g数据库常用操作实例总结

Fawn 2023-07-20

129

一文详解Oracle存储过程

Nafisa 2023-07-20

1415

我要提问

致谢

帮助他人，成就自己。

人生最大成功就是伸出热情而温暖的双手，尽自己所能去帮助身边的每一个人，只要无私的奉献，就会收获到美好的生活。

1024问感谢每一位朋友的帮助和支持。

软件开发网提供编程的基础软件技术培训教程,软件开发编程实例讲解Go,Node,HTML,CSS,Javascript,Python,Java,Ruby,C,PHP,MySQL等软件开发编程语言以及数据开发的基础知识，也提供大量的软件开发在线实例、从入门到精通就在1024问。

育儿网微养生全球行美食街育儿菜谱大全海南旅游女性养狗百科星座