oracle行转列

原创

371690509 2013-04-23 16:25:27 博主文章分类：ORACLE ©著作权

©著作权归作者所有：来自51CTO博客作者371690509的原创作品，请联系作者获取转载授权，否则将追究法律责任

今天写一个sql 行转列的题目，在此做以记录

统计每一个人操作次数，最后按总次数从大到小排序。
    ROWNUM USERNAME   01 02 03 04 05 06 07 08 09 10 11 12      total
---------- ---------- -- -- -- -- -- -- -- -- -- -- -- -- ----------
         1 sxd         2  2  2  2  2  2  2  2  2  1  1  1         21
         2 xlh         1  1  1  1  1  1  1  1  1  0  0  0          9

以下是测试过程

--建表
create table czjl(id int primary key,
                  username varchar2(30),
                  DoDate TIMESTAMP(9)
                  );

--插入数据
insert into czjl values(1 ,'sxd', to_timestamp('2012-1 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(2 ,'sxd', to_timestamp('2012-2 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(3 ,'sxd', to_timestamp('2012-3 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(4 ,'sxd', to_timestamp('2012-4 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(5 ,'sxd', to_timestamp('2012-5 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(6 ,'sxd', to_timestamp('2012-6 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(7 ,'sxd', to_timestamp('2012-7 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(8 ,'sxd', to_timestamp('2012-8 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(9 ,'sxd', to_timestamp('2012-9 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(10,'sxd', to_timestamp('2012-10-12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(11,'sxd', to_timestamp('2012-11-12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(12,'sxd', to_timestamp('2012-12-12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(13,'sxd', to_timestamp('2012-1 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(14,'sxd', to_timestamp('2012-2 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(15,'sxd', to_timestamp('2012-3 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(16,'sxd', to_timestamp('2012-4 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(17,'sxd', to_timestamp('2012-5 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(18,'sxd', to_timestamp('2012-6 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(19,'sxd', to_timestamp('2012-7 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(20,'sxd', to_timestamp('2012-8 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(21,'sxd', to_timestamp('2012-9 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(22,'xlh', to_timestamp('2012-1 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(23,'xlh', to_timestamp('2012-2 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(24,'xlh', to_timestamp('2012-3 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(25,'xlh', to_timestamp('2012-4 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(26,'xlh', to_timestamp('2012-5 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(27,'xlh', to_timestamp('2012-6 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(28,'xlh', to_timestamp('2012-7 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(29,'xlh', to_timestamp('2012-8 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));
insert into czjl values(30,'xlh', to_timestamp('2012-9 -12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6'));

--查询出结果
select a.username,
count(decode(a.doDate,'01',a.id)) AS "01",
count(decode(a.doDate,'02',a.id)) AS "02",
count(decode(a.doDate,'03',a.id)) AS "03",
count(decode(a.doDate,'04',a.id)) AS "04",
count(decode(a.doDate,'05',a.id)) AS "05",
count(decode(a.doDate,'06',a.id)) AS "06",
count(decode(a.doDate,'07',a.id)) AS "07",
count(decode(a.doDate,'08',a.id)) AS "08",
count(decode(a.doDate,'09',a.id)) AS "09",
count(decode(a.doDate,'10',a.id)) AS "10",
count(decode(a.doDate,'11',a.id)) AS "11",
count(decode(a.doDate,'12',a.id)) AS "12",
count(a.id) as "total"
from (select id,username,TO_CHAR(DoDate,'MM') doDate from czjl) a
group by a.username
order by "total" desc

--这里出现了很多小插曲，'01'这种写法一直报错，指导改为"01" (ps:感谢ymm的帮助)

--加上伪列
select rownum,b.* from (
select a.username,
count(decode(a.doDate,'01',a.id)) AS "01",
count(decode(a.doDate,'02',a.id)) AS "02",
count(decode(a.doDate,'03',a.id)) AS "03",
count(decode(a.doDate,'04',a.id)) AS "04",
count(decode(a.doDate,'05',a.id)) AS "05",
count(decode(a.doDate,'06',a.id)) AS "06",
count(decode(a.doDate,'07',a.id)) AS "07",
count(decode(a.doDate,'08',a.id)) AS "08",
count(decode(a.doDate,'09',a.id)) AS "09",
count(decode(a.doDate,'10',a.id)) AS "10",
count(decode(a.doDate,'11',a.id)) AS "11",
count(decode(a.doDate,'12',a.id)) AS "12",
count(a.id) as "total"
from (select id,username,TO_CHAR(DoDate,'MM') doDate from czjl) a
group by a.username
order by "total" desc
) b