某表tb，有两列a，b其值如下
a b
str1 5
str2 6
str2 3
str3 7
str3 2
想要效果a列字符串连接起来，中间逗号隔开，b列数值相加
结果如下
a b
str1，str2，str2，str3，str3 23
------解决方案--------------------
- SQL code
--试试declare @s varchar(200) set @s=''select @[email protected]+a+',' from tbselect left(@s,len(@s)-1) as a,sum(b) as b from tb
------解决方案--------------------
- SQL code
--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([a] varchar(4),[b] int)insert [tbl]select 'str1',5 union allselect 'str2',6 union allselect 'str2',3 union allselect 'str3',7 union allselect 'str3',2declare @str varchar(1000)set @str=''select @[email protected]+','+a from tblselect right(@str,len(@str)-1)+' '+LTRIM(sum(b)) from tblprint @str(无列名)str1,str2,str2,str3,str3 23
------解决方案--------------------
- SQL code
declare @str varchar(1000)set @str=''select @[email protected]+','+a from tblselect SUBSTRING(@str,2,len(@str))+' '+LTRIM(sum(b)) from tblprint @str
------解决方案--------------------
- SQL code
create table tb(a varchar(10),b int)insert into tb values('str1',5)insert into tb values('str2',6)insert into tb values('str2',3)insert into tb values('str3',7)insert into tb values('str3',2)goselect a = stuff((select ',' + a from tb t for xml path('')) , 1 , 1 , '') , sum(b) bfrom tbdrop table tb/*a b------------------------ ---str1,str2,str2,str3,str3 23(1 行受影响)
------解决方案--------------------
select @str=isnull（@str，0）+','+a from tbl