sql高手请来，谁能一条sql查出这样的结果？

with t1 as
(select distinct name from student)

select t1.name,
(select money from student s where s.date = '20140101' and s.name = t1.name),
(select money from student s where s.date = '20140102' and s.name = t1.name),
(select money from student s where s.date = '20140103' and s.name = t1.name)
from t1

我之前试过一种方法，就是使用：
多行合并成一行的函数，
然后再使用一列变多列的函数。

不知道有没有更好的办法

我采用case表达式，如

[code="sql"]
select id,name,sum(case when date='20140501' then money else 0 end) as d1,.....
from student
group by id,name
[/code]

创建个存储过程执行一下。
存储过程：

用游标遍历插入一张临时表中，遍历结束执行select,然后删除临时表中的记录。

执行存储过程。

这个我感觉只能用过程了吧

这个简单用case...when就可以搞定：
select id,name,
sum(case when date='20140501' then money end) as '20140501',
sum(case when date='20140502' then money end) as '20140502',
sum(case when date='20140503' then money end) as '20140503',
sum(case when date='20140504' then money end) as '20140504',
sum(case when date='20140505' then money end) as '20140505'
from student;

[code="java"]
SELECT t.id,t.name,
sum((CASE WHEN t.date = '20140501' THEN t.money END)) AS '20140501',
sum((CASE WHEN t.date = '20140502' THEN t.money END)) AS '20140502',
sum((CASE WHEN t.date = '20140503' THEN t.money END)) AS '20140503',
sum((CASE WHEN t.date = '20140504' THEN t.money END)) AS '20140504',
sum((CASE WHEN t.date = '20140505' THEN t.money END)) AS '20140505'
FROM student t
GROUP BY t.name
[/code]

[code="sql"]
CREATE PROCEDURE pc(IN a VARCHAR(10),
IN b VARCHAR(10),
IN c VARCHAR(10),
IN d VARCHAR(10),
IN e VARCHAR(10))
SELECT t.id,t.name,

sum((CASE WHEN t.date = a THEN t.money END)) AS a,

sum((CASE WHEN t.date = b THEN t.money END)) AS b,

sum((CASE WHEN t.date = c THEN t.money END)) AS c,

sum((CASE WHEN t.date = d THEN t.money END)) AS d,

sum((CASE WHEN t.date = e THEN t.money END)) AS e

FROM student t

GROUP BY t.name
[/code]
调用：
[code="sql"]
call pc('20140501','20140502','20140503','20140504','20140505' );
[/code]

with t as (
select 1 as id,'张三001' as name,'20140501' da_te ,10 as money from dual
union all
select 2,'张三002','20140502',20 from dual
union all
select 3,'张三003','20140503',30 from dual
union all
select 4,'张三004','20140504',40 from dual
union all
select 5,'张三005','20140505',50 from dual
union all
select 6,'张三001','20140502',15 from dual
)
select * from (select min(id) over (partition by name) as id,name,da_te,money from t) t2
pivot(
sum(money)
for da_te in ('20140501','20140502','20140503','20140504','20140505')
)
order by id;