SQL2005下利用XML进行项目的合并与拆分
2010-04-20 00:00:00 来源:WEB开发网把项目串的编码用相应名称代替
------------------------------------------------------------------------
-- Author: HappyFlyStone
-- Date : 2009-01-04 20:57:59
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.(Build 2195: Service Pack 4)
--
------------------------------------------------------------------------
-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(ID NVARCHAR(3),number varchar(20))
Go
INSERT INTO ta
SELECT '001','1,2' UNION ALL
SELECT '002','1,2,3'
GO
-- Test Data: tb
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(ID INT,name NVARCHAR(7))
Go
INSERT INTO tb
SELECT 1,'测试一' UNION ALL
SELECT 2,'测试二' UNION ALL
SELECT 3,'测试三'
GO
--Start
--查询一
SELECT C.ID,NUMBER = STUFF(REPLACE(REPLACE((
SELECT B.NAME AS NAME
FROM TA A
LEFT JOIN
( SELECT ID,NAME
FROM TB
) B
ON CHARINDEX(','+LTRIM(B.ID)+',',','+A.NUMBER+',')>0
WHERE A.ID = C.ID
FOR XML AUTO
),'"/><B NAME="',','),'"/>',''),1,9,'')
FROM TA C
--查询二
SELECT A.ID,NUMBER=STUFF(
(SELECT ','+NAME
FROM TB
WHERE CHARINDEX(','+LTRIM(ID)+',',','+A.NUMBER+',')>0
FOR XML PATH(''),ROOT('R'),TYPE).value('/R[1]','NVARCHAR(MAX)')
,1,1,'')
FROM TA A
--Result:
/*
id number
---- -----------------
001 测试一,测试二
002 测试一,测试二,测试三
(2 行受影响)
*/
--End
更多精彩
赞助商链接