Oracle 行转列(pivot、wm_concat、decode)使用总结

偶然需要了解，学习了这篇文章，转载记录一下

自：http://blog.csdn.net/jxzkin/article/details/7949629

1.创建测试数据

[html]?view plaincopy

1. CREATE?TABLE?CC??
2. ??(Student?NVARCHAR2(2),Course?NVARCHAR2(2),Score?INT??
3. ??);??

[html]?view plaincopy

1. INSERT?into?CC???
2. select?N‘张三‘,N‘语文‘,78?from?dual?union?all??
3. select?N‘张三‘,N‘数学‘,87?from?dual?union?all??
4. select?N‘张三‘,N‘英语‘,82?from?dual?union?all??
5. select?N‘张三‘,N‘物理‘,90?from?dual?union?all??
6. select?N‘李四‘,N‘语文‘,65?from?dual?union?all??
7. select?N‘李四‘,N‘数学‘,77?from?dual?union?all??
8. select?N‘李四‘,N‘英语‘,65?from?dual?union?all??
9. select?N‘李四‘,N‘物理‘,85?from?dual?;??
10. commit;??

希望看到查询結果:?

[html]?view plaincopy

1. 李四?77?85?65?65?292??
2. 张三?87?90?82?78?337??
   2.使用wm_concat方法

[html]?view plaincopy

1. SELECT?STUDENT,WM_CONCAT(SCORE),SUM(SCORE)?FROM?CC?GROUP?BY?STUDENT;??

3.使用Oracle 11g pivot方法

[html]?view plaincopy

1. SELECT?KIN.*,??
2. ??KIN.a+KIN.b+KIN.c+KIN.d?AS?TOTAL??
3. FROM??
4. ??(SELECT???????????????????????????????*??
5. ??FROM?CC?PIVOT?(?MAX(SCORE)?FOR?COURSE?IN?(‘语文‘?AS?A?,?‘数学‘?AS?B,?‘英语‘?AS?C,‘物理‘?AS?D)?)??
6. ??)?KIN;??

4.使用DECODE方法

[html]?view plaincopy

1. SELECT??
2. student,??
3. MAX(decode(COURSE,?‘语文‘,?SCORE))?A,??
4. MAX(DECODE(COURSE,?‘数学‘,?SCORE))?B,??
5. MAX(DECODE(COURSE,?‘英语‘,?SCORE))?C,??
6. MAX(DECODE(COURSE,?‘物理‘,?SCORE))?D,??
7. SUM(SCORE)?TOTAL??
8. FROM??
9. CC??
10. GROUP?BY??
11. student;??

这样的问题，要找出他的关键点来。其实就是行转列，这是一位同学在Itpub上的问题。

问题的解决：

建表:

create table t_result

(d varchar2(10),result varchar2(4));

插入数据:

insert into t_result values (‘2014-01-01‘,‘胜‘);

insert into t_result values (‘2014-01-01‘,‘胜‘);

insert into t_result values (‘2014-01-01‘,‘负‘);

insert into t_result values (‘2014-01-02‘,‘胜‘);

insert into t_result values (‘2014-01-02‘,‘负‘);

insert into t_result values (‘2014-01-02‘,‘负‘);

?

写法如下, 要扫描两次表

select t1.d,t1.c1 ‘胜‘,t2.c2 ‘负‘ from

(select count(result) c1,d from t_result where result = ‘胜‘ group by d) t1

LEFT outer join

(select count(result) c2,d from t_result where result = ‘负‘ group by d) t2

on t1.d = t2.d

行转列：

SELECT d,SUM(decode(result,‘胜‘,1,0)),SUM(decode(result,‘负‘,1,0))

FROM t_result

GROUP BY d

或

select d,

sum(case result when ‘胜‘ then 1 else 0 end )胜,

sum(case result when ‘负‘ then 1 else 0 end )负

from t_result group by d order by d;