竖表变横表case语句

有一个表a1，表中记录是：
user_no acct_month charge
0001 200505 1.00
0001 200506 2.00
0001 200507 3.00
0002 200505 4.00
0002 200506 5.00
0002 200507 6.00
我想显示成这种格式，sql如何写？
user_no ５月金额　６月金额　７月金额
0001       1.00        2.00        3.00
0002       4.00        5.00        6.00

 

select user_no,
sum(decode(acct_month,'200505',charge,0) 5月金额,
sum(decode(acct_month,'200506',charge,0) 6月金额,
sum(decode(acct_month,'200507',charge,0) 7月金额
from a1 group by user_no

 

 

select b1.user_no,
sum(b1.5_charge) AS 5月金额,
sum(b1.6_charge) AS 6月金额,
sum(b1.7_charge) AS 7月金额
from
(select user_no,
(case when acct_month= '200505' then
charge
end ) AS 5_charge,
(case when acct_month= '200506' then
charge
end ) AS 6_charge,
(case when acct_month= '200507' then
charge
end ) AS 7_charge
from a1) b1
group by b1.user_no

 

select user_no,
sum(case acct_month when '200505' then charge else 0 end ) 5月金额,
sum(case acct_month when '200506' then charge else 0 end ) 6月金额,
sum(case acct_month when '200507' then charge else 0 end ) 7月金额
from a1 group by user_no
其实case和decode差不多的，但是case是oracle9i中新增的特性，如果在9i以下的数据库使用是要报错的。

转载于:https://www.cnblogs.com/Godblessyou/archive/2010/07/17/1779613.html