SQL 合并列值和拆分列值

合并列值表結構，資料如下：id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到結果：id values------ -----------1 aa,bb2 aaa,bbb,ccc即：group by id, 求 value 的和（字串相加）1. 舊的解決方案(在sql server 2000中只能用函數解決。)--1. 建立處理函數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')goCREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN  DECLARE @r varchar(8000)  SET @r = ''  SELECT @r = @r + ',' + value FROM tb WHERE id=@id  RETURN STUFF(@r, 1, 1, '')ENDGO-- 調用函數SELECt id, value = dbo.f_str(id) FROM tb GROUP BY iddrop table tbdrop function dbo.f_str/*id value   ----------- -----------1 aa,bb2 aaa,bbb,ccc（所影響的行數為 2 行）*/--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--建立一個合并的函數create function f_hb(@id int)returns varchar(8000)asbegin  declare @str varchar(8000)  set @str = ''  select @str = @str + ',' + cast(value as varchar) from tb where id = @id  set @str = right(@str , len(@str) - 1)  return(@str)Endgo--調用自訂函數得到結果：select distinct id ,dbo.f_hb(id) as value from tbdrop table tbdrop function dbo.f_hb/*id value   ----------- -----------1 aa,bb2 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, ''))Ndrop table tb/*id values----------- -----------1 aa,bb2 aaa,bbb,ccc(2 行受影響)*/--SQL2005中的方法2create 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')goselect id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')from tbgroup by id/*id values----------- --------------------1 aa,bb2 aaa,bbb,ccc(2 row(s) affected)*/drop table tb 拆分列值有表tb, 如下:id value----------- -----------1 aa,bb2 aaa,bbb,ccc欲按id,分拆value列, 分拆後結果如下:id value----------- --------1 aa1 bb2 aaa2 bbb2 ccc1. 舊的解決方案(sql server 2000)SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b  SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)FROM tb A, # BWHERE SUBSTRING(',' + A.[values], 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 行受影響)*/