菜鸟又来求救，继续行转列。。。。

[hanyangjian]

表结构

ID 分类 创建时间
1 A 2014-01-01
2 A 2014-02-01
3 A 2014-03-01
4 b 2014-01-01
5 b 2014-02-01
6 b 2014-03-01
7 c 2014-01-01
8 c 2014-02-01
9 c 2014-03-01

输出结果 2014年

分类 记录总数 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
A 3 1 1 1 0 0 0 0 0 0 0 0 0
B 3 1 1 1 0 0 0 0 0 0 0 0 0
C 3 0 0 0 0 0 0 0 0 0 0 0 0
D 0 0 0 0 0 0 0 0 0 0 0 0 0
E 0 0 0 0 0 0 0 0 0 0 0 0 0

分类固定位5类，求高手帮助

[bw555]

设定你的表名为T

SELECT B.*
FROM (
SELECT 'A' AS 分类 FROM DUAL UNION ALL
SELECT 'B' AS 分类 FROM DUAL UNION ALL
SELECT 'C' AS 分类 FROM DUAL UNION ALL
SELECT 'D' AS 分类 FROM DUAL UNION ALL
SELECT 'E' AS 分类 FROM DUAL
)A,(
SELECT 分类,count(1) 记录总数,
sum(decode(to_char(创建时间,'mm'),'01',1))    "1月",
sum(decode(to_char(创建时间,'mm'),'02',1))    "2月",
sum(decode(to_char(创建时间,'mm'),'03',1))    "3月",
sum(decode(to_char(创建时间,'mm'),'04',1))    "4月",
sum(decode(to_char(创建时间,'mm'),'05',1))    "5月",
sum(decode(to_char(创建时间,'mm'),'06',1))    "6月",
sum(decode(to_char(创建时间,'mm'),'07',1))    "7月",
sum(decode(to_char(创建时间,'mm'),'08',1))    "8月",
sum(decode(to_char(创建时间,'mm'),'09',1))    "9月",
sum(decode(to_char(创建时间,'mm'),'10',1))    "10月",
sum(decode(to_char(创建时间,'mm'),'11',1))    "11月",
sum(decode(to_char(创建时间,'mm'),'12',1))    "12月"
FROM T
where  to_char(创建时间,'yyyy')='2014'
group by 分类
)B
WHERE A.分类(+)=B.分类
order by A.分类

[hanyangjian]

非常谢谢，我测试一下

[bw555]

把结果中的空转化为0

SELECT B.分类,记录总数,
NVL("1月",0) "1月",   NVL("2月",0) "2月",   NVL("3月",0) "3月",   NVL("4月",0) "4月",   
NVL("5月",0) "5月",   NVL("6月",0) "6月",   NVL("7月",0) "7月",   NVL("8月",0) "8月",   
NVL("9月",0) "9月",   NVL("10月",0) "10月",NVL("11月",0) "11月",NVL("12月",0) "12月"
FROM (
SELECT 'A' AS 分类 FROM DUAL UNION ALL SELECT 'B' AS 分类 FROM DUAL UNION ALL
SELECT 'C' AS 分类 FROM DUAL UNION ALL SELECT 'D' AS 分类 FROM DUAL UNION ALL
SELECT 'E' AS 分类 FROM DUAL
)A,(
SELECT 分类,count(1) 记录总数,
sum(decode(to_char(创建时间,'mm'),'01',1))    "1月",sum(decode(to_char(创建时间,'mm'),'02',1))    "2月",
sum(decode(to_char(创建时间,'mm'),'03',1))    "3月",sum(decode(to_char(创建时间,'mm'),'04',1))    "4月",
sum(decode(to_char(创建时间,'mm'),'05',1))    "5月",sum(decode(to_char(创建时间,'mm'),'06',1))    "6月",
sum(decode(to_char(创建时间,'mm'),'07',1))    "7月",sum(decode(to_char(创建时间,'mm'),'08',1))    "8月",
sum(decode(to_char(创建时间,'mm'),'09',1))    "9月",sum(decode(to_char(创建时间,'mm'),'10',1))    "10月",
sum(decode(to_char(创建时间,'mm'),'11',1))    "11月",sum(decode(to_char(创建时间,'mm'),'12',1))    "12月"
FROM T
where  to_char(创建时间,'yyyy')='2014'
group by 分类
)B
WHERE A.分类(+)=B.分类
order by A.分类