不知道是否可以实现，问问大家。需求其实很简单。
表aaa中
aaa 3000 zzz
bbb 3000 zzz
ccc 3000 zzz
上述数据中，是否可以通过一条sql语句聚合上面的3条记录，形成类似下面的一条记录
aaa,bbb,ccc 3000 zzz
其实关键就是第一个字段。
谢谢大家！
------解决方案--------------------
- SQL code
create table aaa(col1 varchar(5),col2 int,col3 varchar(5))insert into aaaselect 'aaa', 3000, 'zzz' union allselect 'bbb', 3000, 'zzz' union allselect 'ccc', 3000, 'zzz'select * from aaacol1 col2 col3----- ----------- -----aaa 3000 zzzbbb 3000 zzzccc 3000 zzzselect stuff((select ','+col1 from aaa b where b.col2=a.col2 and b.col3=a.col3 for xml path('')),1,1,'') col1,a.col2,a.col3from aaa agroup by a.col2,a.col3col1 col2 col3------------- ----------- -----aaa,bbb,ccc 3000 zzz
------解决方案--------------------
- SQL code
--> 测试数据：[aaa]if object_id('[aaa]') is not null drop table [aaa]create table [aaa]([col1] varchar(3),[col2] int,[col3] varchar(3))insert [aaa]select 'aaa',3000,'zzz' union allselect 'bbb',3000,'zzz' union allselect 'ccc',3000,'zzz'goif OBJECT_ID('pro_test')is not nulldrop proc pro_testgocreate proc pro_testasdeclare @col1 varchar(100)declare @col2 varchar(100)declare @col3 varchar(100)set @col1=''set @col2=''set @col3=''select @[email protected]+ltrim(col1)+',' from(select distinct col1 from aaa)aselect @[email protected]+ltrim(col2)+',' from(select distinct col2 from aaa)aselect @[email protected]+ltrim(col3) from(select distinct col3 from aaa)aselect @[email protected][email protected] as valueexec pro_test/*valueaaa,bbb,ccc,3000,zzz*/感觉一条语句不好实现，写了个过程
------解决方案--------------------
自定义函数应该传col2，col3比较适合。
- SQL code
-- functioncreate function getStr(@col2 varchar(50), @col3 varchar(50))returns varchar(100)asbegin declare @s varchar(100) select @s = isnull(@s+',','')+[col1] from aaa where [col2][email protected] and [col3][email protected] return @send-- 执行select yourstr = dbo.getStr(col2,col3),col2,col3 from aaa group by col2,col3