首页 > 代码库 > Oracle 行转列(pivot、wm_concat、decode)使用总结(转载)
Oracle 行转列(pivot、wm_concat、decode)使用总结(转载)
偶然需要了解,学习了这篇文章,转载记录一下
自:http://blog.csdn.net/jxzkin/article/details/7949629
1.创建测试数据
[html]?view plaincopy
- CREATE?TABLE?CC??
- ??(Student?NVARCHAR2(2),Course?NVARCHAR2(2),Score?INT??
- ??);??
[html]?view plaincopy
- INSERT?into?CC???
- select?N‘张三‘,N‘语文‘,78?from?dual?union?all??
- select?N‘张三‘,N‘数学‘,87?from?dual?union?all??
- select?N‘张三‘,N‘英语‘,82?from?dual?union?all??
- select?N‘张三‘,N‘物理‘,90?from?dual?union?all??
- select?N‘李四‘,N‘语文‘,65?from?dual?union?all??
- select?N‘李四‘,N‘数学‘,77?from?dual?union?all??
- select?N‘李四‘,N‘英语‘,65?from?dual?union?all??
- select?N‘李四‘,N‘物理‘,85?from?dual?;??
- commit;??
希望看到查询結果:?
[html]?view plaincopy
- 李四?77?85?65?65?292??
- 张三?87?90?82?78?337??
2.使用wm_concat方法
[html]?view plaincopy
- SELECT?STUDENT,WM_CONCAT(SCORE),SUM(SCORE)?FROM?CC?GROUP?BY?STUDENT;??
3.使用Oracle 11g pivot方法
[html]?view plaincopy
- SELECT?KIN.*,??
- ??KIN.a+KIN.b+KIN.c+KIN.d?AS?TOTAL??
- FROM??
- ??(SELECT???????????????????????????????*??
- ??FROM?CC?PIVOT?(?MAX(SCORE)?FOR?COURSE?IN?(‘语文‘?AS?A?,?‘数学‘?AS?B,?‘英语‘?AS?C,‘物理‘?AS?D)?)??
- ??)?KIN;??
4.使用DECODE方法
[html]?view plaincopy
- SELECT??
- student,??
- MAX(decode(COURSE,?‘语文‘,?SCORE))?A,??
- MAX(DECODE(COURSE,?‘数学‘,?SCORE))?B,??
- MAX(DECODE(COURSE,?‘英语‘,?SCORE))?C,??
- MAX(DECODE(COURSE,?‘物理‘,?SCORE))?D,??
- SUM(SCORE)?TOTAL??
- FROM??
- CC??
- GROUP?BY??
- 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;
Oracle 行转列(pivot、wm_concat、decode)使用总结(转载)