求一SQL语句？请教哥们
UserId       UserName       RoleID
1                   qqq                 A,B


RoleID         Role
A                   1,0,0,1,1
B                   0,1,0,1,1
C                   1,1,1,1,1
如果用户兼俩个角色   A   B

请问怎么构造查询语句得到用户名为qqq的权限集合为1，1，0，1，1

谢谢

------解决方案--------------------
如果 role的长度固定，可以用一句:
declare @a table(UserId int, UserName varchar(10), RoleID varchar(20))
insert @a select 1 , 'qqq ', 'A,B '
insert @a select 2 , 'ggg ', 'C,A,B '

declare @b table(RoleID varchar(10), Role varchar(20))
insert @b select 'A ', '1,0,0,1,1 '
union all select 'B ' , '0,1,0,1,1 '
union all select 'C ' , '1,1,1,1,1 '

select
username,
case when substring(x,1,1)> '0 ' then '1, ' else '0, ' end+
case when substring(x,2,1)> '0 ' then '1, ' else '0, ' end+
case when substring(x,3,1)> '0 ' then '1, ' else '0, ' end+
case when substring(x,4,1)> '0 ' then '1, ' else '0, ' end+
case when substring(x,5,1)> '0 ' then '1 ' else '0 ' end role
from(
select ltrim(sum(cast(replace(role, ', ', ' ') as int))) x,username from @b b,@a a where charindex( ', '+b.roleid+ ', ', ', '+a.roleid+ ', ')> 0 group by username
)bb
------解决方案--------------------
--result
/*
username role
---------- ---------
ggg 1,1,1,1,1
qqq 1,1,0,1,1

（所影响的行数为 2 行）
*/
------解决方案--------------------
create table a(userID int,username varchar(10),roleID varchar(10))
insert into a select 1, 'qqq ', 'A,B '
insert into a select 2 , 'ggg ', 'A,,C '

create table b(roleID varchar(10),role varchar(10))
insert into b
select 'A ', '1,0,0,1,1 ' union all
select 'B ', '0,1,0,1,1 ' union all
select 'C ', '1,0,1,1,0 '

select username,
stuff(
case when sum(convert(int,substring(role,1,1)))> 0 then ',1 ' else ',0 ' end+
case when sum(convert(int,substring(role,2,1)))> 0 then ',1 ' else ',0 ' end+
case when sum(convert(int,substring(role,3,1)))> 0 then ',1 ' else ',0 ' end+
case when sum(convert(int,substring(role,4,1)))> 0 then ',1 ' else ',0 ' end+
case when sum(convert(int,substring(role,5,1)))> 0 then ',1 ' else ',0 ' end,1,1, ' ') AS role
from (
select a.userID,a.username,replace(role, ', ', ' ') as role
from a inner join b
on charindex( ', '+b.roleID+ ', ', ', '+a.roleID+ ', ')> 0) T
group by username
/*

username role
---------- ---------
ggg 1,0,1,1,1
qqq 1,1,0,1,1
*/

drop table a,b
------解决方案--------------------
sum的部份,可能放外面判斷比較好,不然可能超過10個1的話,會進位
------解决方案--------------------