Oracle中行列轉(zhuǎn)換有哪些方法

更新時間：2023年08月02日 10:36:13 作者：客逍京北岸

這篇文章主要給大家介紹了關于Oracle中行列轉(zhuǎn)換有哪些方法的相關資料,最近在工作中遇到了涉及到數(shù)據(jù)庫行列之間相互轉(zhuǎn)換的問題,所以這里給大家總結(jié)介紹下,需要的朋友可以參考下

前言

行列轉(zhuǎn)換是指將行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)，或?qū)⒘袛?shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)的過程。這通常使用的辦法是用PIVOT和UNPIVOT函數(shù)來實現(xiàn)。這里描述兩種方法分別實現(xiàn)行列轉(zhuǎn)換！?。?/p>

首先創(chuàng)建表：

學生表：student;--包括學生號，姓名，年紀，性別，生日
教師表：teacher;--包括教師編號，姓名
課程表：course；--包括課程編號，課程名稱，對應教師
學生成績表：sc；--包括學生號，課程編號，成績

創(chuàng)建表的腳本如下：

--學生 student表
drop table student;
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5),
birthday date
);
--教師 teacher表
drop table teacher;
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
--課程 course表
drop table course;
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
--學生成績 sc表
drop table sc;
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
/*******初始化學生表的數(shù)據(jù)******/
insert into student values ('s001','張亍卬',FLOOR(months_between(SYSDATE,date '2000-3-5')/12),'男',date '2000-3-5');
insert into student values ('s002','李殳戔',FLOOR(months_between(SYSDATE,date '2001-2-3')/12),'男',date '2001-2-3');
insert into student values ('s003','吳仝玓',FLOOR(months_between(SYSDATE,date '2002-5-8')/12),'男',date '2002-5-8');
insert into student values ('s004','琴甪',FLOOR(months_between(SYSDATE,date '2000-6-15')/12),'女',date '2000-6-15');
insert into student values ('s005','王讱纊',FLOOR(months_between(SYSDATE,date '2000-8-12')/12),'女',date '2000-8-12');
insert into student values ('s006','李孖伣',FLOOR(months_between(SYSDATE,date '2001-9-20')/12),'男',date '2001-9-20');
insert into student values ('s007','劉辿吒',FLOOR(months_between(SYSDATE,date '2002-10-5')/12),'男',date '2002-10-5');
insert into student values ('s008','蕭竦俐',FLOOR(months_between(SYSDATE,date '2003-6-1')/12),'女',date '2003-6-1');
insert into student values ('s009','陳閆邠邡',FLOOR(months_between(SYSDATE,date '2001-1-15')/12),'女',date '2001-1-15');
insert into student values ('s010','陳芃伋',FLOOR(months_between(SYSDATE,date '2001-1-9')/12),'女',date '2001-1-9');
commit;
/******************初始化教師表***********************/
insert into teacher values ('t001', '龔陽');
insert into teacher values ('t002', '諶燕');
insert into teacher values ('t003', '武明星');
commit;
/***************初始化課程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t001');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t003');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成績表***********************/
insert into sc values ('s001','c001',78);
insert into sc values ('s002','c001',80);
insert into sc values ('s003','c001',81);
insert into sc values ('s004','c001',60);
insert into sc values ('s001','c002',82);
insert into sc values ('s002','c002',72);
insert into sc values ('s003','c002',81);
insert into sc values ('s001','c007',88);
insert into sc values ('s001','c010',73);
insert into sc values ('s002','c003',69);
insert into sc values ('s002','c008',92);
insert into sc values ('s002','c009',81);
insert into sc values ('s002','c007',85);
insert into sc values ('s002','c010',75);
insert into sc values ('s005','c001',63);
insert into sc values ('s005','c002',96);
insert into sc values ('s005','c007',75);
insert into sc values ('s005','c010',72);
insert into sc values ('s006','c001',72);
insert into sc values ('s007','c001',61);
insert into sc values ('s008','c001',92);
insert into sc values ('s009','c001',58);
insert into sc values ('s010','c001',85);
insert into sc values ('s002','c004',80);
insert into sc values ('s002','c005',70);
insert into sc values ('s002','c006',60);
commit;

一、行轉(zhuǎn)列（一）

使用case when/decode+聚合函數(shù)+group by的方法實現(xiàn)行轉(zhuǎn)列；

把sc表進行行轉(zhuǎn)列查詢出每個學生每門課程的成績：

原sc表:

SELECT * FROM sc;--學生成績表

執(zhí)行結(jié)果展示其中一部分：

此時要對cno課程編號進行行轉(zhuǎn)列：

select sno,sum(case cno when 'c001' then score end) c001,
           sum(case cno when 'c002' then score end) c002,
           sum(case cno when 'c003' then score end) c003,
           sum(case cno when 'c004' then score end) c004,
           sum(case cno when 'c005' then score end) c005,
           sum(case cno when 'c006' then score end) c006,
           sum(case cno when 'c007' then score end) c007,
           sum(case cno when 'c008' then score end) c008,
           sum(case cno when 'c009' then score end) c009,
           sum(case cno when 'c0010' then score end) c0010
from sc
group by sno  
order by sno;

執(zhí)行結(jié)果：

展示的為每個學生他的每一門課程成績；

總結(jié)：

要求把查詢的哪一列轉(zhuǎn)成列名就放在case后面,并把它的列中值進行分類放在when后面；

比如學生成績表總共就三列（學生號，課程編號，學生成績），我們要查詢每個學生的每科成績展示，就需要對課程編號cno進行分類轉(zhuǎn)換，因此把課程編號cno放在case后面，然后把課程編號cno中所包含的所有值進行分類，即全部課程科目c001--c0010，分類放在when的后面！！

要把哪一列內(nèi)容放在列中值中就放在then 后面；

意思就是我們最后要看的結(jié)果值，比如對應上面查詢，要查看的是學生成績score，此時就把學生成績score放在then的后面即可。

這種辦法可以實現(xiàn)我們對需求的解決實現(xiàn)，但是使用比較麻煩，可能會理解錯誤，而且代碼語句寫的比較多，因此可以換種方法來更簡單實現(xiàn)行轉(zhuǎn)列！?。?/p>

二、行轉(zhuǎn)列（二）

使用PIVOT函數(shù)，可以將行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)，并且可以在同一查詢中匯總和篩選數(shù)據(jù)。

基本語法格式如下：

PIVOT(被聚合的列 FOR 行轉(zhuǎn)列的列 in(列中值1,列中值2...))
select *
from  表
pivot (聚合函數(shù)(被聚合的列) for 行轉(zhuǎn)列的列 in (列中值1,,列中值2植..))

批注：

被聚合的列：變成列中值的列；
行轉(zhuǎn)列的列：由列中值變?yōu)榱忻牧?；
列中值1,列中值2..：新增加的列名（即為行轉(zhuǎn)列的列中的列中值）就是列中值1，列中值2...。
備注：被聚合的列要加聚合函數(shù)。

或者另一種理解：

SELECT *
FROM (SELECT column1, column2, column3 FROM table_name)
PIVOT (aggregate_function(column2) 
       FOR column1 IN ('value1' AS alias1, 'value2' AS alias2, ...));

其中，PIVOT中的column1是要轉(zhuǎn)換為列的列，column2是要匯總的列，alias是列的別名。

那么此時“把sc表行轉(zhuǎn)列查詢每個學生每門課程的成績”可寫為：

select *
from sc
pivot(sum(score) for cno in('c001' c001,'c002' c002,'c003' c003,'c004' c004,
                'c005' c005,'c006' c006,'c007' c007,'c008' c008,'c009' c009,'c010' c010))
order by sno;

其中，as可加可不加，對于列中值一定要加單引號。同時運行結(jié)果是和之前的一致。如圖所示：

使用PIVOT函數(shù)時，需要注意以下幾點：

PIVOT函數(shù)必須在FROM子句中使用，因此需要將原始查詢包裝在一個子查詢中。
aggregate_function是要應用于column2的聚合函數(shù)，可以是SUM、AVG、COUNT、MAX、MIN等。
FOR子句指定要在新列中顯示的值。在IN子句中指定這些值，并在別名中指定新列的名稱。

三、列轉(zhuǎn)行（一）

使用union all方法實現(xiàn)列轉(zhuǎn)行；

比如：有一張員工表emp，請用一條sql顯示如下格式

ENPNO KEY VALUE
7369 ENAME SMITH
7369 JOB CLERK
7369 MGR 7902

先看原員工表格式：

select * from emp;

通過對比發(fā)現(xiàn)是將原表中的列和其對應值轉(zhuǎn)換為行式展現(xiàn)，同時為其定義了新的列名分別為ENPNO 、KEY 、VALUE 。那么用union all的方式實現(xiàn)的語句為：

select * from 
  (select empno,'ENAME' KEY,ENAME VALUE FROM EMP 
  UNION ALL
  select empno,'JOB' KEY,TO_CHAR(JOB) VALUE FROM EMP
  UNION ALL
  select empno,'MGR' KEY,TO_CHAR(MGR) VALUE FROM EMP
  UNION ALL
  select empno,'HIREDATE' KEY,TO_CHAR(HIREDATE) VALUE FROM EMP
  UNION ALL
  select empno,'SAL' KEY,TO_CHAR(SAL) VALUE FROM EMP
  UNION ALL
  select empno,'COMM' KEY,TO_CHAR(COMM) VALUE FROM EMP
  UNION ALL
  select empno,'DEPTNO' KEY,TO_CHAR(DEPTNO) VALUE FROM EMP)
WHERE EMPNO=7369;

簡單理解為：查詢該員工編號對應的每一條列信息，對列中值進行格式轉(zhuǎn)換統(tǒng)一，然后使用union all進行并集為一個數(shù)據(jù)集合作為參考表，最后加判斷條件完成列轉(zhuǎn)換。那么看下這種方式的運行結(jié)果：

通過改圖發(fā)現(xiàn)確實已經(jīng)完成了目的需求的格式轉(zhuǎn)換。不過此方法同樣比較繁瑣，代碼量也比較多，所以可以換另外一種方法實現(xiàn)同樣的效果。

四、列轉(zhuǎn)行（二）

UNPIVOT函數(shù)可以將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)。基本語法如下：

unpivot 列轉(zhuǎn)行自動去空如果要留住空值在unpivot 后加上 include nulls

unpivot(被聚合的列的新列名 for 列轉(zhuǎn)行的列的新列名 in (字段1,字段2...))

被聚合的列的新列名：指的是目標結(jié)果集的列名，按照目標結(jié)果集來填寫，即原來聚合的數(shù)據(jù)如這里的nums，列轉(zhuǎn)行之前的列中值放在取了新名字的這個列中；

列轉(zhuǎn)行的列的新列名：指的是要列轉(zhuǎn)行的列名的集合新名字，既創(chuàng)建一個新的列來存儲要列轉(zhuǎn)行的列，如這里的name，他的列中值在列傳行之前為原視圖的多個列；

字段1,字段2...：指的是要列轉(zhuǎn)行的列名，既為要放到列轉(zhuǎn)行的列的新列名里的列中值，就是列轉(zhuǎn)行之前視圖的多個列。

完整格式：

SELECT *
FROM table_name
UNPIVOT (column3 FOR column1 IN (column2, column3, ...));

column1是要轉(zhuǎn)換為行的列，column2和column3是要轉(zhuǎn)換的列。

那么此時使用UNPIVOT函數(shù)完成上個問題的列轉(zhuǎn)行方法就可以寫為：

select *
from (SELECT empno,ENAME,JOB,
             TO_CHAR(MGR) MGR,
             TO_CHAR(HIREDATE) HIREDATE,
             TO_CHAR(SAL) SAL,
             TO_CHAR(COMM) COMM,
             TO_CHAR(DEPTNO) DEPTNO
      FROM EMP
      WHERE EMPNO=7369)
unpivot include nulls(VALUE for KEY IN(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO));

這里在列轉(zhuǎn)行時對表中每一列做了格式統(tǒng)一，最后運行結(jié)果和第一種方法一樣。如圖所示：

使用UNPIVOT函數(shù)時，需要注意以下幾點：

UNPIVOT函數(shù)必須在FROM子句中使用，因此需要將原始查詢包裝在一個子查詢中。
FOR子句指定要轉(zhuǎn)換為行的列。
IN子句指定要轉(zhuǎn)換的列。

總結(jié)

到此這篇關于Oracle中行列轉(zhuǎn)換有哪些方法的文章就介紹到這了,更多相關Oracle行列轉(zhuǎn)換內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

Oracle decode函數(shù)用法詳解
本文詳細講解了Oracle中decode函數(shù)的用法，文中有相關的圖文示例，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下
2023-05-05
深入淺析Oracle數(shù)據(jù)庫管理之創(chuàng)建和刪除數(shù)據(jù)庫
本篇文章給大家介紹oracle數(shù)據(jù)庫管理之創(chuàng)建和刪除數(shù)據(jù)庫，本文從數(shù)據(jù)庫管理概述、數(shù)據(jù)庫管理方法、數(shù)據(jù)庫的準則、使用dbca創(chuàng)建數(shù)據(jù)庫、使用dbca刪除數(shù)據(jù)庫等五大方面展開話題，需要的朋友一起學習吧
2015-10-10
整理匯總Oracle常用命令方便你我他
想統(tǒng)一整理Oracle中常用的命令語句，以后遇到，會不斷更新此博客中。為了以后方便查詢，因此整理此博客中
2012-11-11
Oracle Number型的深入理解
本篇文章是對Oracle中Number型進行了詳細的分析介紹，需要的朋友參考下
2013-05-05
Navicat連接Oracle數(shù)據(jù)庫報錯：Oracle library is not&nb
這篇文章主要介紹了解決Navicat連接Oracle數(shù)據(jù)庫提示oracle library is not loaded的問題,本文通過圖文結(jié)合的形式給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下
2024-06-06
淺析Oracle中char和varchar2的區(qū)別
以下是對Oracle中char和varchar2的區(qū)別進行了分析介紹，需要的朋友可以過來參考下
2013-07-07
Oracle中用戶密碼過期修改為不限制的具體步驟
要在 Oracle 數(shù)據(jù)庫中修改用戶的密碼過期設置,使其不限制密碼過期,可以調(diào)整用戶所關聯(lián)的 Profile,本文通過代碼示例給大家介紹的非常詳細,需要的朋友可以參考下
2025-12-12
oracle數(shù)據(jù)庫導入dmp文件實現(xiàn)方式
文章介紹了將線上Oracle?dmp文件導入本地數(shù)據(jù)庫的步驟：創(chuàng)建用戶并授權(quán),建立表空間（指定數(shù)據(jù)文件路徑、大小及自動擴展）,綁定表空間與用戶,注冊用戶后啟動監(jiān)聽,檢查監(jiān)聽狀態(tài)為READY,最后用指定用戶執(zhí)行導入,強調(diào)文件夾權(quán)限需設置為777以避免報錯
2025-09-09
Oracle報錯記錄被另外一個用戶鎖定的解決方案
這篇文章主要介紹了Oracle報錯記錄被另外一個用戶鎖定的解決方案,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2020-10-10
oracle—SQL技巧之(一)連續(xù)記錄查詢sql案例測試
有這樣一個需求：需要查詢出某個客戶某一年那些天是有連續(xù)辦理過業(yè)務,本文給予sql實現(xiàn)語句并測試，感興趣的朋友可以了解下
2013-01-01