求一个SQL语句,谢谢了
表 AAA里的数据

BH MC
XH20120609002S
XH20120609002
XH20120609002L
XH20120609002XL


用sql语句如何实现成这样的结果:(MC字段如果为空的话则不显示在里面)

BH MC
XH20120609002S,L,XL

[解决办法]

SQL code

--转换这前先把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 Agroup 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 tvgroup by id /*id    ccname1    aa,bb2    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 @tabdeclare @str varchar(5000),@i int =0,@MC nvarchar(20) =''set @str=''while @i<(select count(*) from @tab)beginselect top 1 @MC=MC from @tabset @str=@str+(select MC from @tab where MC=@MC)+','--set @str=@str+','delete from @tab where MC=@MCendselect distinct BH,left(@str,LEN(@str)-1) MC from #AAA/*BH    MCXH20120609002     S,L,XL*/