一个痛苦的问题，请帮忙

[y2jwww]

表1
DepId DepName
01 service
02 acount


表2
StateId DepId message
1 01 getphone
2 01 analys
3 02 acount monny
4 02 bank


表3
StateId DepName message
01 service getphone,analys
02 acount acount monny,bank

表1和表2是基本表，本人想把基本表中的信息insert到表3，请各表帮忙写一条SQL,因为
表1中的值不是固定的。

[hellowork]

if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
if object_id('fnJoin') is not null
drop function fnJoin
GO
----创建测试函数,参数为表2的DepId
create function fnJoin(@id varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + message from tbTest2 where DepId = @id
return stuff(@str,1,1,'')
end
GO
----创建测试表
create table tbTest1(DepId varchar(10),DepName varchar(10))
create table tbTest2(StateId int,DepId varchar(10),message varchar(50))
insert tbTest1
select '01', 'service' union all
select '02', 'acount'
insert tbTest2
select 1, '01', 'getphone' union all
select 2, '01', 'analys' union all
select 3, '02', 'acount monny' union all
select 4, '02', 'bank'
----查询.如果需要生成表3,请使用into
SELECT b.DepId as StateId,b.DepName,max(dbo.fnJoin(a.DepId)) as message --into 表3 /*保存到表3中*/
FROM tbTest2 a
left join tbTest1 b on a.DepId = b.DepId
GROUP BY b.DepId,b.DepName
ORDER BY b.DepId

----清除测试环境
drop table tbTest1,tbTest2
drop function fnJoin

/*结果
StateId DepName message
-------------------------------------------------------
01 service getphone,analys
02 acount acount monny,bank
*/

[y2jwww]

上面写错了一点，应该是
表1
DepId DepName
01 service
02 acount


表2
StateId DepId message
1 01 getphone
2 01 analys
3 02 acount monny
4 02 bank


表3
Id DepName message
1 service getphone,analys
2 acount acount monny,bank

表1和表2是基本表，本人想把基本表中的信息insert到表3，请各表帮忙写一条SQL,因为
表1中的值不是固定的。
hellowork(一两清风)
的答案可能还有点问题。可能是我没有把问题说清楚

[wgsasd311]

--稍改如下
if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
if object_id('tbTest3') is not null
drop table tbTest3
if object_id('fnJoin') is not null
drop function fnJoin
GO
----创建测试函数,参数为表2的DepId
create function fnJoin(@id varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + message from tbTest2 where DepId = @id
return stuff(@str,1,1,'')
end
GO
----创建测试表
create table tbTest1(DepId varchar(10),DepName varchar(10))
create table tbTest2(StateId int,DepId varchar(10),message varchar(50))
create table tbTest3(Id int identity,Depname varchar(10),message varchar(50))
insert tbTest1
select '01', 'service' union all
select '02', 'acount'
insert tbTest2
select 1, '01', 'getphone' union all
select 2, '01', 'analys' union all
select 3, '02', 'acount monny' union all
select 4, '02', 'bank'
----查询.如果需要生成表3,请使用into
insert into tbtest3
SELECT b.DepName,dbo.fnJoin(b.DepId)
FROM tbTest2 a
left join tbTest1 b on a.DepId = b.DepId
GROUP BY b.DepId,b.DepName
ORDER BY b.DepId

select * from tbtest3
----清除测试环境
drop table tbTest1,tbTest2,tbtest3
drop function fnJoin

[老本]

用临时表和游标实现好了！

[xiaoxiangqing]

--drop table tbTest1,tbTest2,tbTest3
if exists(select * from sysobjects where id=object_id('tb1') and type='u')
drop table tb1
if exists(select * from sysobjects where id=object_id('tb3') and type='u')
drop table tb2
if exists(select * from sysobjects where id=object_id('tb3') and type='u')
drop table tb3
go

create table tb1(DepId varchar(10),DepName varchar(10))
create table tb2(StateId int not null ,DepId varchar(10) not null,message varchar(50),link_message varchar(500) null,primary key clustered(DepId,StateId))
create table tb3(DepId varchar(10) not null,Depname varchar(10),message varchar(50))
go

insert tb1
select '01', 'service' union all
select '02', 'acount'
insert tb2(StateId,DepId,message)
select 1, '01', 'getphone' union all
select 2, '01', 'analys' union all
select 3, '02', 'acount monny' union all
select 4, '02', 'bank'
go

declare @DepId varchar(10),@link_mess varchar(500)
set @DepId=''
update tb2 set @link_mess=case when @DepId<>DepId then message else @link_mess+','+message end,@DepId=DepId,link_message=@link_mess


insert tb3
select a.*,max(link_message) link_message from tb1 a join tb2 b on a.DepId=b.DepId group by a.DepId,a.DepName

select * from tb3
/*
StateId DepName message
01 service getphone,analys
02 acount acount monny,bank
*/