一个SQL语句实例

一、问题

有以下两张表A和B

如何生成一下结果？

二、解答

编写如下SQL语句：

use MyTest
go
if object_id('A','table') is not null
drop table A
if object_id('B','table') is not null
drop table B
if object_id('C','table') is not null
drop table C
if object_id('myresult','table') is not null
drop table myresult
go
create table A
(
id int primary key,
Bid varchar(20)
)
create table B
(
Bid int primary key,
Description varchar(20)
)

insert into A values(5,'2,3')
insert into A values(12,'2,3,4')

insert into B values(2,'222')
insert into B values(3,'333')
insert into B values(4,'444')

create table C
(
Cno int identity(1,1) primary key,
Cid int,
Cdes varchar(10)
)

create table myresult
(
Cid int,
Cdes nvarchar(30)
)
--开始游标
declare mycursor cursor
for select * from A
open mycursor

declare @id int,@str varchar(20)
fetch next from mycursor into @id,@str
while(@@fetch_status=0)
begin
     declare @n int,@m int,@c nvarchar(10)
     set @n=1
     set @m=charindex(',',@str)
     while(@m>0)
     begin
         set @c=substring(@str,@n,@m-@n)
         insert into C values(@id,@c)
         set @n=@m+1
         set @m=charindex(',',@str,@n)
     end
     if(@m<=0)
     begin
     set @c=substring(@str,@n,len(@str)-@n+1)
     insert into C values(@id,@c)
     end 
fetch next from mycursor into @id,@str
end
close mycursor
deallocate mycursor


declare mycursor2 cursor
for select C.Cno,C.Cid,B.Description from C,B where cast(C.Cdes as int)=B.Bid group by C.Cid,C.Cno,B.Description
open mycursor2

declare @fno int,@fid int,@fstr nvarchar(30)
fetch next from mycursor2 into @fno,@fid,@fstr
while(@@fetch_status=0)
begin
     if(not exists(select * from myresult where Cid=@fid))
     begin
        insert into myresult values(@fid,@fstr)
     end
     else
     begin
        set @fstr=stuff(@fstr,1,0,',')
        update myresult set Cdes =stuff(@fstr,1,0,Cdes) where Cid=@fid
     end
   fetch next from mycursor2 into @fno,@fid,@fstr 
end
close mycursor2
deallocate mycursor2

select * from myresult

三、求解

请教各位大牛更加简便的解法^v^