【DB笔试面试467】Oracle中行列互换有哪些方法？ - 为之网

C/C++教程

【DB笔试面试467】Oracle中行列互换有哪些方法？

本文主要是介绍【DB笔试面试467】Oracle中行列互换有哪些方法？，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

♣ 题目部分

Oracle中行列互换有哪些方法？

♣ 答案部分

行列转换包括以下六种情况：（1）列转行。（2）行转列。（3）多列转换成字符串。（4）多行转换成字符串。（5）字符串转换成多列。（6）字符串转换成多行。其中，重点是行转列和字符串转换成多行。

下面将分别对这几种情况举例来说明。

1、列转行

列转行就是将原表中的列名作为转换后的表的内容。列转行主要采用UNION ALL来完成。示例代码如下所示：

CREATE TABLE TEST_LHR

(

NAME VARCHAR2(255),

JANUARY NUMBER(18),

FEBRUARY NUMBER(18),

MARCH NUMBER(18),

APRIL NUMBER(18),

MAY NUMBER(18)

);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('长寿', 58, 12, 26, 18, 269);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('璧山', 33, 18, 17, 16, 206);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('杨家坪', 72, 73, 79, 386, 327);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('巫溪', 34, 9, 7, 21, 33);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('丰都', 62, 46, 39, 36, 91);

INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)

VALUES ('武隆', 136, 86, 44, 52, 142);

COMMIT;

SELECT * FROM TEST_LHR;

下面进行列转换：

SELECT *

FROM (SELECT T.NAME, 'JANUARY' MONTH, T.JANUARY V_NUM

FROM TEST_LHR T

UNION ALL

SELECT T.NAME, 'FEBRUARY' MONTH, T.FEBRUARY V_NUM

FROM TEST_LHR T

UNION ALL

SELECT T.NAME, 'MARCH' MONTH, T.MARCH V_NUM

FROM TEST_LHR T

UNION ALL

SELECT T.NAME, 'APRIL' MONTH, T.APRIL V_NUM

FROM TEST_LHR T

UNION ALL

SELECT T.NAME, 'MAY' MONTH, T.MAY V_NUM

FROM TEST_LHR T)

ORDER BY NAME;

2、行转列

行转列就是将行数据内容作为列名。示例代码如下所示：

CREATE TABLE T_ROW_COL_LHR(

NUM VARCHAR2(15 CHAR),

NAME VARCHAR2(20 CHAR),

SEX VARCHAR2(2 CHAR),

CLASSES VARCHAR2(30 CHAR),

COURSE_NAME VARCHAR2(50 CHAR)

);

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206211','王艺','男','06-1班','保险学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','保险学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','财务管理');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','财务会计');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','电子商务');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','公共经济学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','公司理财');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','管理学原理');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','保险学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','保险学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','财务管理');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','财务会计');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','电子商务');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','公共经济学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','环境管理学');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','管理学原理');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','商务谈判');

INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206216','李佳琪','男','06-2','土地估计');

SELECT * FROM T_ROW_COL_LHR;

将COURSE_NAME进行行转列：

SELECT NUM,NAME,SEX,CLASSES,

MAX(DECODE(RN,1,COURSE_NAME,NULL)) COURSE_NAME_1,

MAX(DECODE(RN,2,COURSE_NAME,NULL)) COURSE_NAME_2,

MAX(DECODE(RN,3,COURSE_NAME,NULL)) COURSE_NAME_3,

MAX(DECODE(RN,4,COURSE_NAME,NULL)) COURSE_NAME_4,

MAX(DECODE(RN,5,COURSE_NAME,NULL)) COURSE_NAME_5

FROM (SELECT NUM,NAME,SEX,CLASSES,COURSE_NAME,

ROW_NUMBER() OVER(PARTITION BY NUM,NAME,SEX,CLASSES ORDER BY COURSE_NAME) RN

FROM T_ROW_COL_LHR)

GROUP BY NUM,NAME,SEX,CLASSES;

结果如下所示：

将COURSE_NAME列合并，示例代码如下所示：

SELECT NUM,

NAME,

SEX,

CLASSES,

(MAX(DECODE(RN, 1, COURSE_NAME, NULL)) ||

MAX(DECODE(RN, 2, ',' || COURSE_NAME, NULL)) ||

MAX(DECODE(RN, 3, ',' || COURSE_NAME, NULL)) ||

MAX(DECODE(RN, 4, ',' || COURSE_NAME, NULL)) ||

MAX(DECODE(RN, 5, ',' || COURSE_NAME, NULL))) NAME

FROM (SELECT NUM,

NAME,

SEX,

CLASSES,

COURSE_NAME,

ROW_NUMBER() OVER(PARTITION BY NUM, NAME, SEX, CLASSES ORDER BY COURSE_NAME) RN

FROM T_ROW_COL_LHR)

GROUP BY NUM,

NAME,

SEX,

CLASSES;

结果如下所示：

3、多列转换成字符串

使用||或CONCAT函数实现。示例代码如下所示。

SELECT CONCAT('A','B') FROM DUAL;

4、多行转换成字符串

示例代码如下所示：

CREATE TABLE T_ROW_STR(

ID INT,

COL VARCHAR2(10)

);

INSERT INTO T_ROW_STR VALUES(1,'A');

INSERT INTO T_ROW_STR VALUES(1,'B');

INSERT INTO T_ROW_STR VALUES(1,'C');

INSERT INTO T_ROW_STR VALUES(2,'A');

INSERT INTO T_ROW_STR VALUES(2,'D');

INSERT INTO T_ROW_STR VALUES(2,'E');

INSERT INTO T_ROW_STR VALUES(3,'C');

COMMIT;

SELECT * FROM T_ROW_STR;

SELECT ID,

MAX(DECODE(RN, 1, COL, NULL)) ||

MAX(DECODE(RN, 2, ',' || COL, NULL)) ||

MAX(DECODE(RN, 3, ',' || COL, NULL)) STR

FROM (SELECT ID,

COL,

ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) AS RN

FROM T_ROW_STR) T

GROUP BY ID

ORDER BY 1;

也可以使用SYS_CONNECT_BY_PATH来实现：

SELECT T.ID ID,

MAX(SUBSTR(SYS_CONNECT_BY_PATH(T.COL, ','), 2)) STR

FROM (SELECT ID,

COL,

ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) RN

FROM T_ROW_STR) T

START WITH RN = 1

CONNECT BY RN = PRIOR RN + 1

AND ID = PRIOR ID

GROUP BY T.ID;

5、字符串转换成多列

实际上就是一个字符串拆分的问题。示例代码如下所示：

CREATE TABLE T_COL_ROW(

ID INT,

C1 VARCHAR2(10),

C2 VARCHAR2(10),

C3 VARCHAR2(10));

INSERT INTO T_COL_ROW VALUES (1, 'v11', 'v21', 'v31');

INSERT INTO T_COL_ROW VALUES (2, 'v12', 'v22', NULL);

INSERT INTO T_COL_ROW VALUES (3, 'v13', NULL, 'v33');

INSERT INTO T_COL_ROW VALUES (4, NULL, 'v24', 'v34');

INSERT INTO T_COL_ROW VALUES (5, 'v15', NULL, NULL);

INSERT INTO T_COL_ROW VALUES (6, NULL, NULL, 'v35');

INSERT INTO T_COL_ROW VALUES (7, NULL, NULL, NULL);

COMMIT;

SELECT * FROM T_COL_ROW;

CREATE TABLE T_STR_COL AS

SELECT ID,C1||','||C2||','||C3 AS C123

FROM T_COL_ROW;

SELECT * FROM T_STR_COL;

SELECT ID,

C123,

SUBSTR(C123, 1, INSTR(C123 || ',', ',', 1, 1) - 1) C1,

SUBSTR(C123,

INSTR(C123 || ',', ',', 1, 1) + 1,

INSTR(C123 || ',', ',', 1, 2) - INSTR(C123 || ',', ',', 1, 1) - 1) C2,

SUBSTR(C123,

INSTR(C123 || ',', ',', 1, 2) + 1,

INSTR(C123 || ',', ',', 1, 3) - INSTR(C123 || ',', ',', 1, 2) - 1) C3

FROM T_STR_COL

ORDER BY 1;

6、字符串转换成多行

示例代码如下所示：

CREATE TABLE T_STR_ROW AS

SELECT ID,

MAX(DECODE(RN, 1, COL, NULL)) ||

MAX(DECODE(RN, 2, ',' || COL, NULL)) ||

MAX(DECODE(RN, 3, ',' || COL, NULL)) STR

FROM (SELECT ID,

COL,

ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) AS RN

FROM T_ROW_STR) T

GROUP BY ID

ORDER BY 1;

SELECT * FROM T_STR_ROW;

SELECT ID,

1 AS P,

SUBSTR(STR, 1, INSTR(STR || ',', ',', 1, 1) - 1) AS CV

FROM T_STR_ROW

UNION ALL

SELECT ID,

2 AS P,

SUBSTR(STR,

INSTR(STR || ',', ',', 1, 1) + 1,

INSTR(STR || ',', ',', 1, 2) - INSTR(STR || ',', ',', 1, 1) - 1) AS CV

FROM T_STR_ROW

UNION ALL

SELECT ID,

3 AS P,

SUBSTR(STR,

INSTR(STR || ',', ',', 1, 1) + 1,

INSTR(STR || ',', ',', 1, 2) - INSTR(STR || ',', ',', 1, 1) - 1) AS CV

FROM T_STR_ROW

ORDER BY 1,

2;

还有几类特殊的转换，如下所示：

CREATE OR REPLACE TYPE INS_SEQ_TYPE IS VARRAY(8) OF NUMBER;

SELECT * FROM TABLE(INS_SEQ_TYPE(1, 2, 3, 4, 5));

结果：

COLUMN_VALUE

------------

1

2

3

4

5

若是字符串类型，则如下所示：

CREATE OR REPLACE TYPE INS_SEQ_TYPE2 IS VARRAY(80) OF VARCHAR2(32767);

SELECT * FROM TABLE(INS_SEQ_TYPE2('aadf,dea','cbc','d'));

结果：

COLUMN_VALUE

-----------------

aadf,dea

cbc

d

还有如下的形式：

先创建一个TYPE类型，代码如下：

CREATE OR REPLACE TYPE TYPE_STR_LHR IS TABLE OF VARCHAR2(32767);

再创建FUN_SPLIT2_LHR函数，代码如下：

CREATE OR REPLACE FUNCTION FUN_SPLIT2_LHR(P_STR VARCHAR2,

V_SPLIT VARCHAR2 DEFAULT ',') RETURN TYPE_STR_LHR IS

RS TYPE_STR_LHR := TYPE_STR_LHR();

V_STR VARCHAR2(4000) := '';

V_LEN NUMBER := 0;

BEGIN

V_STR := P_STR;

V_LEN := LENGTH(V_SPLIT);

WHILE LENGTH(V_STR) > 0 LOOP

IF INSTR(V_STR, V_SPLIT) > 0 THEN

RS.EXTEND;

RS(RS.COUNT) := SUBSTR(V_STR, 1, INSTR(V_STR, V_SPLIT) - 1);

V_STR := SUBSTR(V_STR, INSTR(V_STR, V_SPLIT) + V_LEN);

ELSE

RS.EXTEND;

RS(RS.COUNT) := V_STR;

EXIT;

END IF;

END LOOP;

RETURN RS;

END;

测试如下：

SQL> SELECT COLUMN_VALUE FROM TABLE(FUN_SPLIT2_LHR('101,102,103',','));

COLUMN_VALUE

------------------

101

102

103

SQL> SELECT TO_NUMBER(COLUMN_VALUE) FROM TABLE(FUN_SPLIT2_LHR('101,102,103'));

TO_NUMBER(COLUMN_VALUE)

-----------------------

101

102

103

SQL> SELECT COLUMN_VALUE FROM TABLE(FUN_SPLIT2_LHR('101@#102@#103','@#'));

COLUMN_VALUE

---------------

101

102

103

& 说明：

有关行列互换更多的案例可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-1272538/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

---------------优质麦课------------

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

详细内容可以添加麦老师微信或QQ私聊。

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● QQ：646634621 QQ群：618766405

● 提供OCP、OCM和高可用部分最实用的技能培训

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

本文分享自微信公众号 - DB宝（lhrdba）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

这篇关于【DB笔试面试467】Oracle中行列互换有哪些方法？的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

您可能喜欢

栏目导航

前端开发

HTML5教程

CSS教程

Javascript

jQuery教程

AJAX教程

Node.js教程

XML教程

正则表达式

后端开发

Go教程

C/C++教程

消息队列MQ

Net Core教程

Asp.net教程

Java教程

PHP教程

移动端开发

微信公众号开发

小程序开发

Swift教程

IOS教程

Kotlin教程

Android开发

数据库

Redis教程

MongoDB教程

PostgreSQL教程

Oracle教程

MariaDB教程

SqLite教程

MySql教程

SqlServer教程

服务器运维

Kubernetes

Docker容器

linux shell

Nginx教程

网站安全

PowerShell教程

Linux教程

人工智能

TensorFlow教程

Python教程

机器学习

人工智能学习

区块链

区块链技术

游戏开发

游戏编程

Unity3D教程

网站运营

网站策划

网站优化

建站知识

大数据/云计算

云计算

Hadoop教程

软件工程

软件/开发工具使用

Git教程

资讯