父子结构的表输出问题
有3个表
表一 主表
AID UserName
1 a
2 b

表二 副表
BID LevelName
1 aa
2 bb
3 aaa
4 bbbb
5 ccccc

表三 关系表
ID AID BID
1 1 1
2 1 2
3 2 3
4 2 4
5 2 5

现在 一个UserName 有多个关联多个LevelName

怎么在一行记录中输出呢
例如
AID UserName LevelName
1 a aa,bb
2 b aaa,bbbb,ccccc

[解决办法]

if object_id('Tempdb..#A') is not null drop table #A
if object_id('Tempdb..#B') is not null drop table #B
if object_id('Tempdb..#C') is not null drop table #C

--临时表A
create table #A
(
[AID] int identity(1,1) not null,
[UserName] nvarchar(10) null
)
 --临时表B
create table #B
(
[BID] int identity(1,1) not null,
[LevelName] nvarchar(10) null
)
--临时表C
create table #C
(
id int identity(1,1) not null,
[AID] int null,
[BID] int null
)
-----------添加测试数据---------
Insert into #A
 select 'a' union all
 select 'b'

Insert into #B
 select 'aa' union all
 select 'bb' union all
 select 'aaa' union all
 select 'bbbb' union all 
 
 select 'ccccc' 

Insert into #C
 select 1,1 union all
 select 1,2 union all
 select 2,3 union all
 select 2,4 union all
 select 2,5
-- 

-------------查询-------------
;with cte as(
select a.AID,a.UserName,b.LevelName from #a a
join #c c on a.AID=c.AID 
join #b b on b.BID=c.BID
)
select AID,UserName, stuff((select ','+LevelName  from cte z where  t.AID=z.AID  for xml path('')), 1, 1, '')  LevelName
from cte t
group by AID,UserName

-------------------------------------------
----------结果------------

AID         UserName   LevelName
----------- ---------- ----------------------------------------------------------------------------------------------------------------
1           a          aa,bb
2           b          aaa,bbbb,ccccc

(2 行受影响)

[解决办法]

create table 主表
(AID int,UserName varchar(10))

insert into 主表
 select 1, 'a' union all
 select 2, 'b'

create table 副表
(BID int, LevelName varchar(10))

insert into 副表
 select 1, 'aa' union all
 select 2, 'bb' union all
 select 3, 'aaa' union all
 select 4, 'bbbb' union all
 select 5, 'ccccc'

create table 关系表 
(ID int, AID int, BID int)
 
insert into 关系表
 select 1, 1, 1 union all
 select 2, 1, 2 union all
 select 3, 2, 3 union all
 select 4, 2, 4 union all
 select 5, 2, 5


select a.AID,
       a.UserName,
       stuff((select ','+LevelName
              from 关系表 c 
 
              inner join 副表 b on c.BID=b.BID
              where c.AID=a.AID
              for xml path('')),1,1,'') 'LevelName'
 from 主表 a

/*
AID         UserName   LevelName
----------- ---------- ---------------------
1           a          aa,bb
2           b          aaa,bbbb,ccccc

(2 row(s) affected)
*/

[解决办法]
select ID,b1.AID,UserName,LevelName into #temp from b1,b2,b3 where
b1.AID=b3.AID and b2.BID=b3.BID
go
select AID,UserNam,LevelName=
stuff((select ','+rtrim(LevelName) from #temp where t.UserName=UserName
order by LevelName for xml path('')),1,1,'')
from #temp t group by AID,UserName