竖变横的小问题

[alice_art]

有三张表:
用户表：userID,userName,age,tel
角色表：roleID,roleName
用户角色对应表：userID,roleID


一个用户可以有多个角色，重点是如何使角色(roleName)用顿号连起来？

比如说结果为：
userID userName age tel roleName
1 alice 19 139000000 总经理、管理员、财务


我试了很久都没有成功，还得请教大家，谢谢。

[lzhcxc]

create table A(userID int,userName nvarchar(10),age int,tel nvarchar(10))
insert A select 1, 'alice', 19, '139000000'

create table B(roleID int,roleName nvarchar(10))
insert B select 1, '总经理'
union all select 2, '管理员'
union all select 3, '财务'

create table C(userID int,roleID int)
insert C select 1, 1
insert C select 1, 2


create function fun(@userID int)
returns nvarchar(100)
as
begin
declare @re nvarchar(100)
set @re=''
select @re=@re+'、'+B.roleName
from C
inner join B on C.roleID=B.roleID
where C.userID=@userID

return stuff(@re, 1, 1, '')
end

select *, dbo.fun(userID) as roleName
from A

[lzhcxc]

--result
userID userName age tel roleName
----------- ---------- ----------- ---------- -------------------
1 alice 19 139000000 总经理、管理员

(1 row(s) affected)

[qys2000]

create table A(userID int,userName nvarchar(10),age int,tel nvarchar(10))
insert A select 1, 'alice', 19, '139000000'

create table B(roleID int,roleName nvarchar(10))
insert B select 1, '总经理'
union all select 2, '管理员'
union all select 3, '财务'

create table C(userID int,roleID int)
insert C select 1, 1
insert C select 1, 2


create function fun(@userID int)
returns nvarchar(100)
as
begin
declare @re nvarchar(100)
set @re=''
select @re=@re+'、'+B.roleName
from C
inner join B on C.roleID=B.roleID
where C.userID=@userID

return stuff(@re, 1, 1, '')
end

select *, dbo.fun(userID) as roleName
from A