列转逗号分隔的形式解决方法

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'goselect 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,100021002       100031003       100041004       10004,10005(4 行受影响)*/godrop table tb
------解决方案--------------------
參照方法
SQL code
-> --> (Roy)生成測試數據 if not object_id('Tab') is null    drop table TabGoCreate table Tab([Col1] int,[Col2] nvarchar(1))Insert Tabselect 1,N'a' union allselect 1,N'b' union allselect 1,N'c' union allselect 2,N'd' union allselect 2,N'e' union allselect 3,N'f'Go合并表:SQL2000用函数:goif object_id('F_Str') is not null    drop function F_Strgocreate function F_Str(@Col1 int)returns nvarchar(100)asbegin    declare @S nvarchar(100)    select @S=isnull(@S+',','')+Col2 from Tab where [email protected]    return @SendgoSelect distinct Col1,Col2=dbo.F_Str(Col1) from TabgoSQL2005用XML:方法1:select     a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')from     (select distinct COl1 from Tab) aCross 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) across apply    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE)                .query('<Tab>                {for $i in /Tab[position()<last()][email protected] return concat(string($i),",")}                {concat("",string(/Tab[last()][email protected]))}                </Tab>')                )bSQL05用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,c2           d,e3           f(3 行受影响)*/
------解决方案--------------------
SQL code
use Tempdbgo--> -->  if not object_id(N'T') is null    drop table TGoCreate table T([goodsid] int,[sheetid] int)Insert Tselect 1001,10001 union allselect 1001,10002 union allselect 1002,10003 union allselect 1003,10004 union allselect 1004,10004 union allselect 1004,10005Goselect     a.[goodsid],[sheetid]=replace(b.[sheetid].value('/T[1]','nvarchar(max)'),char(44)+char(32),char(44))from     (select distinct [goodsid] from T) across apply    (select [sheetid]=(select [sheetid] from T  where [goodsid]=a.[goodsid] FOR XML AUTO, TYPE)                .query('<T>                {for $i in /T[position()<last()][email protected] return concat(string($i),",")}                {concat("",string(/T[last()][email protected]))}                </T>')                )b/*goodsid    sheetid1001    10001,100021002    100031003    100041004    10004,10005*/