create table tb(goodsid varchar(10),sheetid varchar(10))
insert into tb select '1001','10001'
insert into tb select '1001','10002'
insert into tb select '1002','10003'
insert into tb select '1003','10004'
insert into tb select '1004','10004'
insert into tb select '1004','10005'
go
select goodsid,stuff((select ','+sheetid from tb where goodsid=a.goodsid for xml path('')),1,1,'')sheetid from tb a group by goodsid
/*goodsid    sheetid
---------- --------------------------------------------------------------------------
1001       10001,10002
1002       10003
1003       10004
1004       10004,10005

(4 行受影响)

*/
go
drop table tb

------解决方案--------------------
參照方法
SQL code
-> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go

合并表:

SQL2000用函数:

go
if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
    return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab

go

SQL2005用XML:

方法1:

select 
    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')
from 
    (select distinct COl1 from Tab) a
Cross apply
    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b

方法2:

select 
    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
from 
    (select distinct COl1 from Tab) a
cross apply
    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE)
                .query('<Tab>
                {for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}
                {concat("",string(/Tab[last()]/@COl2))}
                </Tab>')
                )b

SQL05用CTE:

;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab)
,Roy2 as
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 
union all 
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1)
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0)


生成结果:
/*
Col1        COl2
----------- ------------
1           a,b,c
2           d,e
3           f

(3 行受影响)
*/

------解决方案--------------------
SQL code
use Tempdb
go
--> --> 
 
if not object_id(N'T') is null
    drop table T
Go
Create table T([goodsid] int,[sheetid] int)
Insert T
select 1001,10001 union all
select 1001,10002 union all
select 1002,10003 union all
select 1003,10004 union all
select 1004,10004 union all
select 1004,10005

Go
select 
    a.[goodsid],[sheetid]=replace(b.[sheetid].value('/T[1]','nvarchar(max)'),char(44)+char(32),char(44))
from 
    (select distinct [goodsid] from T) a
cross apply
    (select [sheetid]=(select [sheetid] from T  where [goodsid]=a.[goodsid] FOR XML AUTO, TYPE)
                .query('<T>