字段值拆分与合并方法

--==============================================
作者：王运亮(wwwwgou)
时间：2011-06-07
博客：http://blog.csdn.net/wwwwgou
--==============================================

--字段拆分:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
 DROP TABLE #temp
GO
CREATE TABLE #temp
(
 id INT IDENTITY,
 [name] NVARCHAR(MAX)
)
INSERT #temp
SELECT N'王一' UNION ALL
SELECT N'赵二,刘三' UNION ALL
SELECT N'李四,张五,钱六'
GO

--#1.SQL2000 辅助表拆分方法
SELECT
 a.id,
 [name] = SUBSTRING(a.[name], b.number, CHARINDEX(',', a.[name]+',', b.number) - b.number)
FROM #temp a
 INNER JOIN master..spt_values b
  ON b.type = 'p' AND b.number BETWEEN 1 AND LEN(a.[name])
WHERE SUBSTRING(','+a.[name], b.number, 1) = ','

--#2.SQL2005 Xml方法
SELECT
 a.id,
 b.[name]
FROM
(SELECT id, [name]=CONVERT(XML, '<root><v>'+replace([name],',','</v><v>')+'</v></root>') FROM #temp) a
OUTER APPLY
(SELECT [name] = C.v.value('.','NVARCHAR(MAX)') FROM a.[name].nodes('/root/v') C(v)) b

--#3.SQL2005 CTE递归方法
;WITH cte AS 
(
 SELECT
  id,
  [name] = CAST(LEFT([name], CHARINDEX(',', [name] + ',') - 1) AS NVARCHAR(MAX)),
  Split = CAST(STUFF([name] + ',', 1, CHARINDEX(',', [name] + ','), '') AS NVARCHAR(MAX))
 FROM #temp
 UNION ALL
 SELECT
  id,
  [name] = CAST(LEFT(Split, CHARINDEX(',', Split) - 1) AS NVARCHAR(MAX)),
  Split = CAST(STUFF(Split, 1, CHARINDEX(',', Split), '') AS NVARCHAR(MAX))
 FROM cte
 WHERE Split > ''
)
SELECT
    id,
    [name]
FROM cte
ORDER BY id
OPTION (MAXRECURSION 0) --递归次数-0代表无限制

--字段合并:
IF OBJECT_ID('dbo.temp') IS NOT NULL
 DROP TABLE dbo.temp
GO
CREATE TABLE dbo.temp
(
 id INT,
 [name] NVARCHAR(MAX)
)
INSERT dbo.temp
SELECT 1, N'王一' UNION ALL
SELECT 1, N'赵二' UNION ALL
SELECT 2, N'刘三'
GO
--#1.sql2000中只能用自定义的函数
IF OBJECT_ID('dbo.fn_merger', 'FN') IS NOT NULL
 DROP FUNCTION dbo.fn_merger
GO
CREATE FUNCTION fn_merger(@id INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
 DECLARE @rv NVARCHAR(MAX)
 SET @rv = N''
 SELECT @rv = @rv + ',' + [name] FROM dbo.temp WHERE id = @id
 RETURN STUFF(@rv,1,1,'')
END
GO
--TEST:
SELECT
 id,
 [name] = dbo.fn_merger(id)
FROM temp
GROUP BY id

--#2.xml方法
SELECT
 id,
 [name] = STUFF((SELECT ','+[name] FROM temp WHERE id=T.id FOR XML PATH('')),1,1,'')
FROM temp T
GROUP BY id

--清除测试数据
DROP TABLE #temp
DROP TABLE temp