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 NT5.0(Build 2195: Service Pack 4)
--
------------------------------------------------------------------------
-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(pid INT,tid INT,name NVARCHAR(6))
Go
INSERT INTO ta
SELECT 1,1,'hy3500' UNION ALL
SELECT 1,2,'aabbcc' UNION ALL
SELECT 2,3,'1111' UNION ALL
SELECT 2,4,'2222'
GO
-- Test Data: tb
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(NAME NVARCHAR(2),id INT)
Go
INSERT INTO tb
SELECT '型号',1 UNION ALL
SELECT '参数',2
GO
--Start
SELECT t.[name],A,B
FROM(
SELECT B.[NAME], CAST((SELECT [name]
FROM TA
WHERE PID = A.PID FOR XML PATH('')) AS XML) AS X
FROM TA A
LEFT JOIN TB B ON A.PID = B.ID
GROUP BY B.[NAME],A.PID
) t
CROSS APPLY
(SELECT A=t.x.value('/name[1]','VARCHAR(10)'),B = t.x.value('/name[2]','VARCHAR(10)')) M
--Result:
/*
c a b
---- ---------- ----------
参数 1111 2222
型号 hy3500 aabbcc
(2 行受影响)
*/
--End
更多精彩
赞助商链接