Oracle行专列的几种方法
1.创建表t_result
create table t_result
(d varchar2(10),result varchar2(4));
2.往表中插入数据
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 * from t_result;
解决办法一：
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 * from t_result pivot (count(result) for result in('胜','负'))
解决办法三：
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;
取自：https://www.cnblogs.com/markfeifei/p/4009343.html
自己做过测试，然后外加一种方法
例子二：
CREATE TABLE studentScores
(
userName varchar(20),
subject varchar(30),
score FLOAT
);
INSERT INTO studentScores(username,subject,score) values( '张三', '语文', 80);
INSERT INTO studentScores values ('张三', '数学', 90);
INSERT INTO studentScores values ('张三', '英语', 70);
INSERT INTO studentScores values ('张三', '生物', 85);
INSERT INTO studentScores values ('李四', '语文', 80);
INSERT INTO studentScores values ('李四', '数学', 92);
INSERT INTO studentScores values ('李四', '英语', 76);
INSERT INTO studentScores values ('李四', '生物', 88);
INSERT INTO studentScores values ('码农', '语文', 60);
INSERT INTO studentScores values ('码农', '数学', 82);
INSERT INTO studentScores values ('码农', '英语', 96);
INSERT INTO studentScores values ('码农', '生物', 78);
select * from studentScores;
select username,WM_CONCAT(score),SUM(SCORE) from studentScores group by username;
解决办法一：
SELECT KIN.*,
KIN.a+KIN.b AS TOTAL
FROM
(SELECT *
FROM studentScores PIVOT (sum(SCORE) FOR subject IN ('语文' AS A , '数学' AS B) )
) KIN;
解决办法二：
SELECT
username,
MAX(DECODE(subject, '语文', SCORE)) A,
MAX(DECODE(subject, '数学', SCORE)) B,
MAX(DECODE(subject, '英语', SCORE)) C,
MAX(DECODE(subject, '生物', SCORE)) D,
SUM(SCORE) TOTAL
FROM
studentScores
GROUP BY
username;
