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;
posted on 2014-10-07 14:53 Mark_fei 阅读(34370) 评论(0) 编辑 收藏 举报