oracle中行转列

下面我们来模拟：

SQL> select * from test;

      YEAR      MONTH     AMOUNT
---------- ---------- ----------
      2014          1        1.1
      2014          2        1.2
      2015          1        2.1
      2015          2        2.2

SQL> select year,
  2  sum(decode(month,'1',amount,'0')) as m1,
  3  sum(decode(month,'2',amount,'0')) as m2
  4  from test group by year;

      YEAR         M1         M2
---------- ---------- ----------
      2014        1.1        1.2
      2015        2.1        2.2

对于为什么要使用sum（）函数有点疑问

SQL> select year,
  2  decode(month,'1',amount,'0') as m1,
  3  decode(month,'2',amount,'0') as m2
  4  from test group by year;
decode(month,'1',amount,'0') as m1,
       *
ERROR at line 2:
ORA-00979: not a GROUP BY expression

可以发现是因为使用了group by分组函数才需要sum函数。

二、下面我们来实现列转行：

SQL> select * from test_tb_grade2;
 
USER_NAME               CN_SCORE MATH_SCORE   EN_SCORE
--------------------- ---------- ---------- ----------
lisi                          87         65         75
xixi                          87         65         75

要实现如下的查询效果：

SQL> select * from test_tb_grade2;
 
USER_NAME               CN_SCORE MATH_SCORE   EN_SCORE
--------------------- ---------- ---------- ----------
lisi                          87         65         75
xixi                          87         65         75

1、利用SQL里面的union:
select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2  
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2  
union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2  
order by user_name,COURSE  

2、也可以利用【 insert all into ... select 】来实现，首先需要先建一个表TEST_TB_GRADE3：
Sql代码 
create table TEST_TB_GRADE3   
    (  
      USER_NAME VARCHAR2(20 CHAR),   
      COURSE    VARCHAR2(20 CHAR),   
      SCORE     FLOAT   
    )   
 再执行下面的sql：
 
Sql代码 
insert all 
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '语文', CN_SCORE) 
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '数学', MATH_SCORE) 
into test_tb_grade3(USER_NAME,COURSE,SCORE) values(user_name, '英语', EN_SCORE) 
select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2; 
commit; 
 别忘记commit操作，然后再查询TEST_TB_GRADE3，发现表中的数据就是列转成行

文章借鉴：http://www.2cto.com/database/201108/100792.html