请问这个sql怎么写？

表A有数据
ID NAME
1 I
1 have
1 a
1 dream
1 !
2 Hello
2 everybody

查询结果：
ID NAME
1 I have a dream!
2 Hello everybody

请问这个查询结果应该用怎样的sql语句能够查询出来？谢谢！

SqlServer2005
[code="java"]declare @t table (
id int,
name varchar(20)
)

insert into @t (id, name) values(1, 'i')
insert into @t (id, name) values(1, 'have')
insert into @t (id, name) values(1, 'a')
insert into @t (id, name) values(1, 'dream')
insert into @t (id, name) values(1, '!')
insert into @t (id, name) values(2, 'hello')
insert into @t (id, name) values(2, 'everybody')

select id, name = stuff((select ' '+ rtrim(name) from @t where id = t.id for xml path('')), 1, 1, '')
from @t t
group by id[/code]

oracle：select t1.name || t1.name from tablename t1 where t1.id=t1.id

写出来效率也不怎么高，改用存储过程吧。

MySQL:
[code="sql"]
select id, group_concat(name SEPARATOR ' ') from Test group by id;
[/code]
[url=http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat]group_concat参考[/url]

可以看看[url=http://www.websitefactors.co.uk/mysql/2011/07/mysql-limit-the-values-in-the-group_concat-function/]group_concat例子[/url]

oracle估计就要自己写函数了，可以参考[url=http://foolraty.iteye.com/]foolraty[/url]的[url=http://foolraty.iteye.com/blog/630460]mysql的group_concat对应oracle的wm_concat[/url]