列转逗号分隔的形式
源表
goodsid sheetid
1001 10001
1001 10002
1002 10003
1003 10004
1004 10004
1004 10005
想要变成group by goodsid的形式
goodsid sheeetid
1001 10001，10002
1002 10003
1003 10004
1004 10004，10005
数据无限多，goodsid有重复，sheetid有重复
我试过declare sql varchar(8000)
set @sql=''
select @sql=@sql+sheetid+',' from table
where goodsid='1001'
这个只能取出一列的数据来，不能分组取
请各位高手指点
[解决办法]
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
[解决办法]
參照方法
-> --> (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 Col1=@Col1 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()]/@COl2 return concat(string($i),",")} {concat("",string(/Tab[last()]/@COl2))} </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 行受影响)*/
[解决办法]
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()]/@sheetid return concat(string($i),",")} {concat("",string(/T[last()]/@sheetid))} </T>') )b/*goodsid sheetid1001 10001,100021002 100031003 100041004 10004,10005*/
[解决办法]
合并列值 --*******************************************************************************************表结构，数据如下： id value ----- ------ 1 aa 1 bb 2 aaa 2 bbb 2 ccc 需要得到结果： id values ------ ----------- 1 aa,bb 2 aaa,bbb,ccc 即：group by id, 求 value 的和（字符串相加） 1. 旧的解决方法(在sql server 2000中只能用函数解决。) --=============================================================================create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go --1. 创建处理函数 CREATE FUNCTION dbo.f_strUnite(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @str varchar(8000) SET @str = '' SELECT @str = @str + ',' + value FROM tb WHERE id=@id RETURN STUFF(@str, 1, 1, '') END GO -- 调用函数 SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id drop table tb drop function dbo.f_strUnite go/* id value ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc （所影响的行数为 2 行） */ --===================================================================================2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go -- 查询处理 SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM tb N WHERE id = A.id FOR XML AUTO ), ' <N value="', ','), '"/>', ''), 1, 1, '') )N drop table tb /* id values ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc (2 行受影响) */ --SQL2005中的方法2 create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') from tb group by id /* id values ----------- -------------------- 1 aa,bb 2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb /*标题：分拆列值1作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：2008-11-20地点：广东深圳描述有表tb, 如下:id value----------- -----------1 aa,bb2 aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id value----------- --------1 aa1 bb2 aaa2 bbb2 ccc*/--1. 旧的解决方法(sql server 2000)SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)FROM tb A, # BWHERE SUBSTRING(',' + A.[value], B.id, 1) = ','DROP TABLE #--2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))insert into tb values(1,'aa,bb')insert into tb values(2,'aaa,bbb,ccc')goSELECT A.id, B.valueFROM( SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb)AOUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v))BDROP TABLE tb/*id value----------- ------------------------------1 aa1 bb2 aaa2 bbb2 ccc(5 行受影响)*/
[解决办法]
--sql server 2005写法，要是2000就用自定义函数吧if object_id('tb','U') is not null drop table tbgocreate table tb( goodsid int, sheetid int)goinsert into tbselect 1001,10001 union allselect 1001,10002 union allselect 1002,10003 union allselect 1003,10004 union allselect 1004,10004 union allselect 1004,10005goselect goodsid,sheeetid=stuff((select ','+cast(sheetid as varchar) from tb where goodsid=a.goodsid for xml path('')),1,1,'') from tb a group by goodsidgo/*goodsid sheeetid----------- ----------------------------------------------------------------------------------------------------------------1001 10001,100021002 100031003 100041004 10004,10005(4 行受影响)*/
[解决办法]