--转换这前先把MC字段为空的数据过滤掉，就可以达到你要的效果
select
    BH,
    MC = stuff((select ','+MC from AAA B where B.BH = A.BH and isnull(B.MC, '') <> '' for xml path('')), 1,1, '')
from AAA A
group by BH

------解决方案--------------------
select BH,stuff((select isnull((',' + MC),'') from temp for xml path('')),1,1,'') from temp
group by BH;

------解决方案--------------------
探讨

for xml path('') 这地方报错呀


------解决方案--------------------
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

------解决方案--------------------
SQL code
declare @tb table (id int, value varchar(10)) 
insert into @tb values(1, 'aa') 
insert into @tb values(1, 'bb') 
insert into @tb values(2, 'aaa') 
insert into @tb values(2, 'bbb') 
insert into @tb values(2, 'ccc')

select id , [value]=
stuff((select ','+[value] from @tb t where id =tv.id for xml path('')), 1, 1, '') 
from @tb as tv
group by id 
/*
id    ccname
1    aa,bb
2    aaa,bbb,ccc
*/

------解决方案--------------------
SQL code
create table #AAA(BH nvarchar(20) null,MC nvarchar(100) null)
insert into #AAA values('XH20120609002 ','S'),
('XH20120609002 ',''),
('XH20120609002','L'),('XH20120609002','XL')

declare @tab table(BH nvarchar(20) null,MC nvarchar(100) null)
insert into @tab select * from #AAA where MC <>''
--select * from @tab
declare @str varchar(5000),@i int =0,@MC nvarchar(20) =''
set @str=''
while @i<(select count(*) from @tab)
begin
select top 1 @MC=MC from @tab
set @str=@str+(select MC from @tab where MC=@MC)+','
--set @str=@str+','
delete from @tab where MC=@MC
end
select distinct BH,left(@str,LEN(@str)-1) MC from #AAA
/*
BH    MC
XH20120609002     S,L,XL
*/