Oracle 行列转换函数

create table exam_record
(id integer primary key,
username varchar2(40),
classtype varchar2(40),
classname varchar2(200),
score number(20,2)
);
insert into exam_record values(1,'张三','文科','语文',97);
insert into exam_record values(2,'李四','理科','数学',80);
insert into exam_record values(3,'王五','理科','化学',90);
insert into exam_record values(4,'张三','文科','英语',88);
insert into exam_record values(5,'张三','理科','化学',92);
insert into exam_record values(6,'李四','理科','物理',70);
insert into exam_record values(7,'王五','文科','语文',67);
insert into exam_record values(8,'李四','文科','语文',100);
insert into exam_record values(9,'王五','文科','历史',79);

select * from exam_record;

wKioL1k3lpewFlwtAAAo9EoCxfk999.png

1.  oracle行转列(连接字符串函数)

  1. 方法一:wmsys.wm_concat(column)     

介绍:其函数在Oracle 10g推出,在10g版本中,返回字符串类型,在11g版本中返回clob类型。括号里面的参数是列,而且可以是多个列的集合,也就是说在括号里面可以自由地用‘||’合并字符串。如下面的例子:

selectusername,to_char(wmsys.wm_concat(classname||':'||score)) "成绩单" from exam_record group by username;

 wKioL1k3lpjR_DjEAAAPdS1AVdM564.png

  1. 方法二:listagg(column,[,]) within group (order by ) [over (partition by  )]          

介绍:其函数在Oracle 11g 版本中推出,对分组后的数据按照一定的排序进行字符串连接。其中,“[,]”表示字符串连接的分隔符,如果选择使用[over(partition by )]则会使其变成分析函数;

selectusername, listagg(classname||':'||score,';') within group (order by id desc)"成绩单
" from exam_recordgroup by username;

wKiom1k3lpihECh6AAAPKlK3VZA543.png

  1. 方法三:sys_connect_by_path(column,<分隔符>)         

介绍:其函数在Oracle 9i 版本中推出,用来合并链路的字符串。注意的是其一定要和connect by子句合用!

第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符。 

SELECT username,LTRIM(MAX(SYS_CONNECT_BY_PATH(classname||':'||score, '//')), '//') "成绩单"  
FROM (SELECT classname, score , username,ROW_NUMBER() OVER(PARTITION BY username ORDER BY id DESC) RN  
          FROMexam_record)  
START WITH RN = 1 
CONNECT BY RN - 1 = PRIOR RN  
       ANDusername = PRIOR username  
GROUP BY username;

wKiom1k3lpmB71x8AAAQgD_8Ku8595.png


Oracle 11g中,Oracle 又增加了2个查询:pivot(行转列)unpivot(列转行)

2. pivot 列转行

语法规范:

SELECT ....FROM    PIVOT    (      aggregate-function()      FOR IN (‘’, ‘’,..., )        ) ASWHERE .....

注意: pivot(聚合函数for 列名 in(类型)),其中 in(‘’) 中可以指定别名,该行是必需的,因此不幸的是,您需要预先知道可能的值。该限制在 XML 格式的查询将有所放宽(略。。。)

in中不可以指定子查询,比如select distinct classnamefrom exam_record;

select *
  from (select username, classname, score
          from exam_record) pivot(max(score) 
               forclassname in ('语文' 语文, '数学' 数学, '英语' 英语, '物理' 物理, '化学' 化学, '历史' 历史, '生物' 生物)
         );

pivot(聚合函数 for 列名 in(类型),这里的列名可以是多个,比如

pivot(max(score) for(classtype,classname) in (('文科','语文'), ('理科','数学'))
select *
  from (selectusername, classtype, classname, score
          fromexam_record) pivot ( max(score) for (classtype,classname) in (('文科','语文'),('理科','数学'))
         );

wKioL1k3lpnheMsnAAAL0gPqS1A937.png

pivot(聚合函数 for 列名 in(类型),这里的聚合函数可以是多个,比如

select *
  from (selectusername, classtype,  score
          fromexam_record) pivot ( sum(score) sum_score,avg(score) avg_score  for (classtype) in (('文科'),('理科'))
         );

wKioL1k3lprTLSXdAAASfuJUh2Q865.png


3. unpivot 行转列

unpivot 可以是 pivot 的反向操作,但不要以为前者可以对后者所进行的任何操作进行反向操作。

create table user_scores  as 
select *
  from (selectusername, classname, score
          fromexam_record) pivot(max(score) 
              for classname in ('语文' 语文, '数学' 数学, '英语' 英语, '物理' 物理, '化学' 化学, '历史' 历史, '生物' 生物)
         );
         
select * from user_scores;


wKiom1k3lpuCxTK2AAARdcHhXCw949.png

 

select username, classname, score
  from user_scores unpivot(score for classnamein (语文, 数学, 英语, 物理, 化学, 历史, 生物));

wKioL1k3lpuRLDkvAAAanc5XT2k056.png

XML类型

上述pivot列转行示例中,你已经知道了需要查询的类型有哪些,用in()的方式包含,假设如果您不知道都有哪些值,您怎么构建查询呢?

pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以XML 格式创建执行了 pivot 操作的输出,在此输出中,您可以指定一个特殊的子句 ANY 而非文字值

示例如下:

select *
  from (selectusername, classname, score from exam_record)       
       pivotxml(max(score) for classname in (any))
 order by 1 desc;

XML中也可以使用子查询代替未知的列值

select *
  from (selectusername, classname, score from exam_record)       
       pivotxml(max(score) for classname in (select distinct classname from exam_record))
 order by 1 desc;


wKiom1k3lpzC5DH8AAALUztYoi8537.png