--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

create function dbo.f_str(@id int) returns varchar(100)
as
begin
    declare @str varchar(1000)
    set @str = ''
    select @str = @str + ',' + cast(value as varchar) from tb where id = @id
    set @str = right(@str , len(@str) - 1)
    return @str
end
go

--调用函数
select id , value = dbo.f_str(id) from tb group by id

drop function dbo.f_str
drop table tb


--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by id

drop table tb

------解决方案--------------------
SQL code
Create table CompanyName (CompanyID int,CompanyName varchar(10),date datetime)
insert CompanyName values(1,'公司1',getdate())
insert CompanyName values(2,'公司2',getdate())
insert CompanyName values(3,'公司3',getdate())

Create table Job (JobID int,Re_CompanyID int,JobPost varchar(20),date datetime)
insert Job values(1,1,'SQL Server DBA',getdate())
insert Job values(2,1,'Delphi',getdate())
insert Job values(3,1,'Oracle DBA',getdate())
insert Job values(4,2,'ASP.Net',getdate())
insert Job values(5,3,'C#',getdate())
GO
--样式一
select a.*,b.JobPost from CompanyName a join Job b on a.companyid=b.Re_CompanyID order by a.CompanyName,b.date
/*
CompanyID   CompanyName date                    JobPost
----------- ----------- ----------------------- --------------------
1           公司1         2009-05-31 11:03:20.793 SQL Server DBA
1           公司1         2009-05-31 11:03:20.793 Delphi
1           公司1         2009-05-31 11:03:20.793 Oracle DBA
2           公司2         2009-05-31 11:03:20.793 ASP.Net
3           公司3         2009-05-31 11:03:20.793 C#
*/

--样式二
GO
--创建一个函数用于合并字符串
create function dbo.f_str
    (@id int) 
    returns varchar(1000)
begin
    declare @str varchar(1000)
    select @str = isnull(@str + ',','') +JobPost from job where Re_CompanyID = @id order by date
    return @str
end
go
select *,JobPost=dbo.f_str(CompanyID) from CompanyName order by CompanyName
/*
CompanyID   CompanyName date                    JobPost
----------- ----------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           公司1         2009-05-31 11:05:33.560 SQL Server DBA,Delphi,Oracle DBA
2           公司2         2009-05-31 11:05:33.577 ASP.Net
3           公司3         2009-05-31 11:05:33.577 C#

(3 行受影响)
*/