有这样的一个需求，附带测试脚本，不用你敲多少测试代码的，谢谢

有这样的一个需求，附带测试脚本，不用你敲多少测试代码的，多谢。

SQL code

if object_id('tempdb..#temp') is not null Begin    truncate table #temp    drop table #tempEndcreate 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  newColumns27    aaa,bbb    4    3团队,1散客3    aaa    4    3团队*/--不知道我描述的可清楚--感谢select * from #tempif object_id('tempdb..#temp') is not null Begin    truncate table #temp    drop table #tempEnd

SQL code

if object_id('tempdb..#temp') is not null  Begintruncate table #tempdrop table #tempEndcreate 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 newColumns27 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 agroup by goods ,withNumif object_id('tempdb..#temp') is not null  Begintruncate table #tempdrop table #tempEnd
[解决办法]
大致如下：
SQL code
CREATE FUNCTION dbo.f_str(@goods varchar(10))RETURNS varchar(8000)ASBEGIN    DECLARE @r varchar(8000)    SET @r = ''    SELECT @r = @r + ',' + value    FROM tb    WHERE @goods =@goods     RETURN STUFF(@r, 1, 1, '')ENDGO-- 调用函数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) agroup by goods ,withNum/*goods    withNum    counts    newColumnsaaa    4    3    3团队aaa,bbb    4    27    3团队,1散客*/