Oracle行转列和列转行

偏执的太偏执、 2022-08-04 14:52 292阅读 0赞

一、行转列  
1.1、初始测试数据  
  
表结构：TEST\_TB\_GRADE  
  
Sql代码:  
  
1    create table TEST\_TB\_GRADE  
2    (  
3      ID        NUMBER(10) not null,  
4      USER\_NAME VARCHAR2(20 CHAR),  
5      COURSE    VARCHAR2(20 CHAR),  
6      SCORE     FLOAT  
7    )  
  
初始数据如下图：

![Oracle行转列和列转行 - Magicc - 異次元藍客][Oracle_ - Magicc -]

1.2、 如果需要实现如下的查询效果图：

![Oracle行转列和列转行 - Magicc - 異次元藍客][Oracle_ - Magicc - 1]

这就是最常见的行转列，主要原理是利用decode函数、聚集函数(sum)，结合group by分组实现的，具体的sql如下：  
  
Sql代码:  
1    select t.user\_name,  
2      sum(decode(t.course, '语文', score,null)) as CHINESE,  
3      sum(decode(t.course, '数学', score,null)) as MATH,  
4      sum(decode(t.course, '英语', score,null)) as ENGLISH  
5    from test\_tb\_grade t  
6    group by t.user\_name  
7    order by t.user\_name  
1.3、延伸  
  
如果要实现对各门功课的不同分数段进行统计，效果图如下：

![Oracle行转列和列转行 - Magicc - 異次元藍客][Oracle_ - Magicc - 2]

具体的实现sql如下：  
Sql代码:  
  
01    select t2.SCORE\_GP,  
02      sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE,  
03      sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH,  
04      sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISH  
05    from (  
06      select t.course,  
07             case when t.score  <60 then '00-60'  
08                  when t.score >=60 and t.score <80  then '60-80'  
09                  when t.score >=80 then '80-100' end as SCORE\_GP,  
10             count(t.score) as COUNTNUM  
11      FROM test\_tb\_grade t  
12      group by t.course,   
13            case when t.score  <60  then '00-60'  
14                  when t.score >=60 and t.score <80  then '60-80'  
15                  when t.score >=80 then '80-100' end  
16      order by t.course ) t2  
17    group by t2.SCORE\_GP  
18    order by t2.SCORE\_GP  
二、列转行  
  
1.1、初始测试数据  
表结构： TEST\_TB\_GRADE2   
Sql代码:    
1    create table TEST\_TB\_GRADE2  
2    (  
3      ID         NUMBER(10) not null,  
4      USER\_NAME  VARCHAR2(20 CHAR),  
5      CN\_SCORE   FLOAT,  
6      MATH\_SCORE FLOAT,  
7      EN\_SCORE   FLOAT  
8    )  
  
初始数据如下图：

![Oracle行转列和列转行 - Magicc - 異次元藍客][Oracle_ - Magicc - 3]

1.2、 如果需要实现如下的查询效果图：

![Oracle行转列和列转行 - Magicc - 異次元藍客][Oracle_ - Magicc - 4]

这就是最常见的列转行，主要原理是利用SQL里面的union，具体的sql语句如下：  
Sql代码:   
  
1    select user\_name, 'CN\_SCORE' COURSE , CN\_SCORE as SCORE from test\_tb\_grade2   
2    union  
3    select user\_name, 'MATH\_SCORE' COURSE, MATH\_SCORE as SCORE from test\_tb\_grade2   
4    union  
5    select user\_name, 'EN\_SCORE' COURSE, EN\_SCORE as SCORE from test\_tb\_grade2   
6    order by user\_name,COURSE  
  
也可以利用【insert all into ... select】来实现，首先需要先建一个表TEST\_TB\_GRADE3：  
Sql代码:    
1    create table TEST\_TB\_GRADE3    
2    (   
3          USER\_NAME VARCHAR2(20 CHAR),    
4          COURSE    VARCHAR2(20 CHAR),    
5          SCORE     FLOAT    
6    )  
     
 再执行下面的sql：   
  Sql代码:    
1    insert all  
2    into test\_tb\_grade3(USER\_NAME,COURSE,SCORE) values(user\_name, '语文', CN\_SCORE)  
3    into test\_tb\_grade3(USER\_NAME,COURSE,SCORE) values(user\_name, '数学', MATH\_SCORE)  
4    into test\_tb\_grade3(USER\_NAME,COURSE,SCORE) values(user\_name, '英语', EN\_SCORE)  
5    select user\_name, CN\_SCORE, MATH\_SCORE, EN\_SCORE from test\_tb\_grade2;  
6    commit;

别忘记commit操作，然后再查询TEST\_TB\_GRADE3，发现表中的数据就是列转成行了。

参考链接：http://www.2cto.com/database/201108/100792.html

[Oracle_ - Magicc -]: http://img0.ph.126.net/l-LuLbh65--6sEeb6kptGQ==/3148860564562213137.png
[Oracle_ - Magicc - 1]: http://img0.ph.126.net/0AlcpCEExWIHjew9MLtMGw==/1961880587773690197.png
[Oracle_ - Magicc - 2]: http://img1.ph.126.net/qm-8Z6E5y1XTvZKaBA-nWA==/5717508842947008775.png
[Oracle_ - Magicc - 3]: http://img1.ph.126.net/YNX6S8JN542JUpuYeQHJsg==/6597332549936777263.png
[Oracle_ - Magicc - 4]: http://img2.ph.126.net/66AqvV7R3Jn5rIGswbV7fA==/6597846021867453968.png