--==============================================
作者:王运亮(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