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;

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。