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;