表 AAA里的数据BH MC
XH20120609002 S
XH20120609002
XH20120609002 L
XH20120609002 XL
用sql语句如何实现成这样的结果:(MC字段如果为空的话则不显示在里面)BH MC
XH20120609002 S,L,XL
XH20120609002 S
XH20120609002
XH20120609002 L
XH20120609002 XL
用sql语句如何实现成这样的结果:(MC字段如果为空的话则不显示在里面)BH MC
XH20120609002 S,L,XL
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
group by BH;
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
*/
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
*/
上面语句主要通过循环来字符串拼接实现你要的效果,相比使用for xml path()来说有一些缺陷,不过你可以作为一种参考,在此基础上更加完善一些。