if object_id('tempdb..#temp') is not null 
Begin
    truncate table #temp
    drop table #temp
End

create table #temp
(
counts int null,
goods varchar(20) null,
withNum int null,
Gift int null,
td varchar(10) null
)

insert into #temp(counts,goods,withnum,gift,td)
    values(23,'aaa,bbb',4,3,'团队')
insert into #temp(counts,goods,withnum,gift,td)
    values(3,'aaa',4,3,'团队')
insert into #temp(counts,goods,withnum,gift,td)
    values(4,'aaa,bbb',4,1,'散客')

/*
现在的显示结果是：
23    aaa,bbb    4    3    团队
3    aaa    4    3    团队
4    aaa,bbb    4    1    散客
*/

/*
我要的结果是
counts  goods   withNum  newColumns
27    aaa,bbb    4    3团队,1散客
3    aaa    4    3团队
*/

--不知道我描述的可清楚
--感谢
select * from #temp

if object_id('tempdb..#temp') is not null 
Begin
    truncate table #temp
    drop table #temp
End

if object_id('tempdb..#temp') is not null  
Begin
truncate table #temp
drop table #temp
End

create table #temp
(
counts int null,
goods varchar(20) null,
withNum int null,
Gift int null,
td varchar(10) null
)

insert into #temp(counts,goods,withnum,gift,td)
values(23,'aaa,bbb',4,3,'团队')
insert into #temp(counts,goods,withnum,gift,td)
values(3,'aaa',4,3,'团队')
insert into #temp(counts,goods,withnum,gift,td)
values(4,'aaa,bbb',4,1,'散客')

/*
现在的显示结果是：
23 aaa,bbb 4 3 团队
3 aaa 4 3 团队
4 aaa,bbb 4 1 散客
*/

/*
我要的结果是
counts goods withNum newColumns
27 aaa,bbb 4 3团队,1散客
3 aaa 4 3团队
*/

--不知道我描述的可清楚
--感谢
select * from #temp

;with t as (
select goods,withNum,counts,convert(varchar(2),Gift)+td as newColumns from #temp
)

select goods ,withNum, [newColumns]=stuff((select ','+[newColumns] from t b where a.goods=b.goods and a.withNum = b.withNum for xml path('')), 1, 1, '') 
from t a
group by goods ,withNum


if object_id('tempdb..#temp') is not null  
Begin
truncate table #temp
drop table #temp
End

------解决方案--------------------
大致如下：
SQL code

CREATE FUNCTION dbo.f_str(@goods varchar(10))
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHERE @goods =@goods 
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt goods, values=dbo.f_str(id) 
FROM tb 
GROUP BY goods

------解决方案--------------------
SQL code


create table temp
(
counts int null,
goods varchar(20) null,
withNum int null,
Gift int null,
td varchar(10) null
)

insert into temp(counts,goods,withnum,gift,td)
values(23,'aaa,bbb',4,3,'团队')
insert into temp(counts,goods,withnum,gift,td)
values(3,'aaa',4,3,'团队')
insert into temp(counts,goods,withnum,gift,td)
values(4,'aaa,bbb',4,1,'散客')

 
select * from temp 


select goods ,withNum,sum(counts) as counts, [newColumns]=stuff((select ','+[newColumns] 
from (select goods,withNum,counts,convert(varchar(2),Gift)+td as newColumns from temp) b 
where a.goods=b.goods and a.withNum = b.withNum for xml path('')), 1, 1, '') 
from (select goods,withNum,counts,convert(varchar(2),Gift)+td as newColumns from temp) a
group by goods ,withNum


/*
goods    withNum    counts    newColumns
aaa    4    3    3团队
aaa,bbb    4    27    3团队,1散客
*/