数据库SQL Server列值链式合并
需求:原始表的数据的结构如图
1所示,把相同guid的code值生成一个链式字符串。
![](http://www.jzxue.com/System/uploads/allimg/101020/2220100.jpg)
(图
1)
目标:我们希望达到的效果如图
2所示,这里的guid变成唯一的了,这行的记录中包含了这个guid所对应的code字段值的链式字符串。
![](http://www.jzxue.com/System/uploads/allimg/101020/2220101.jpg)
(图
2)
分析与实现:要实现图
1到图2的转变,这使用SQL Server 2005的新功能:XML,下面我们来讲讲具体的实现:
1. 首先我们先创建一个测试表,方便后面的效果展现;
--
创建表
if exists ( select * from sysobjects where id = OBJECT_ID ( ' [TempTable_Base] ' ) and OBJECTPROPERTY (id, ' IsUserTable ' ) = 1 )
DROP TABLE [ TempTable_Base ]
CREATE TABLE [ TempTable_Base ] (
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ guid ] [ varchar ] ( 50 ) NULL ,
[ code ] [ varchar ] ( 50 ) NULL )
SET IDENTITY_INSERT [ TempTable_Base ] ON
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 1 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' A ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 2 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' C ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 3 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' E ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 4 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 5 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' G ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 6 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' A ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 7 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 8 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' E ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 9 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' F ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 10 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 11 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' B ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 12 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' D ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 13 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' F ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 14 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 15 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' D ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 16 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' F ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 17 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' C ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 18 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' U ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 19 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' F ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 20 , ' 4802F0CD-B53F-A3F5-1C78-2D7424579C06 ' , ' A ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 21 , ' 3CCBFF9F-827B-6639-4780-DA7215166728 ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 22 , ' 3CCBFF9F-827B-6639-4780-DA7215166728 ' , ' M ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 23 , ' 3CCBFF9F-827B-6639-4780-DA7215166728 ' , ' C ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 24 , ' 3CCBFF9F-827B-6639-4780-DA7215166728 ' , ' M ' )
SET IDENTITY_INSERT [ TempTable_Base ] OFF
SELECT * FROM [ TempTable_Base ]
if exists ( select * from sysobjects where id = OBJECT_ID ( ' [TempTable_Base] ' ) and OBJECTPROPERTY (id, ' IsUserTable ' ) = 1 )
DROP TABLE [ TempTable_Base ]
CREATE TABLE [ TempTable_Base ] (
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ guid ] [ varchar ] ( 50 ) NULL ,
[ code ] [ varchar ] ( 50 ) NULL )
SET IDENTITY_INSERT [ TempTable_Base ] ON
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 1 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' A ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 2 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' C ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 3 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' E ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 4 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 5 , ' 91E92DCB-141A-30B2-E6CD-B59EABD21749 ' , ' G ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 6 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' A ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 7 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 8 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' E ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 9 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' F ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 10 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 11 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' B ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 12 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' D ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 13 , ' 79DD7AB9-CE57-9431-B020-DF99731FC99D ' , ' F ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 14 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 15 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' D ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 16 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' F ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 17 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' C ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 18 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' U ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 19 , ' D61651D9-1B0A-0362-EE91-A805AA3E08F2 ' , ' F ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 20 , ' 4802F0CD-B53F-A3F5-1C78-2D7424579C06 ' , ' A ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 21 , ' 3CCBFF9F-827B-6639-4780-DA7215166728 ' , ' O ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 22 , ' 3CCBFF9F-827B-6639-4780-DA7215166728 ' , ' M ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 23 , ' 3CCBFF9F-827B-6639-4780-DA7215166728 ' , ' C ' )
INSERT [ TempTable_Base ] ( [ id ] , [ guid ] , [ code ] ) VALUES ( 24 , ' 3CCBFF9F-827B-6639-4780-DA7215166728 ' , ' M ' )
SET IDENTITY_INSERT [ TempTable_Base ] OFF
SELECT * FROM [ TempTable_Base ]
2. 使用
SQL Server2005的FOR XML PATH把记录数据以XML的格式组织起来,把同一个guid的数据进行字符串的拼凑。执行下面的SQL就可以达到图3所示的效果了。
--
列值链式合并
SELECT B.guid, LEFT (UserList, LEN (UserList) - 1 ) as paths FROM (
SELECT guid,
( SELECT code + ' , ' FROM TempTable_Base WHERE guid = A.guid ORDER BY ID FOR XML PATH( '' )) AS UserList
FROM TempTable_Base A
GROUP BY guid
) B
SELECT B.guid, LEFT (UserList, LEN (UserList) - 1 ) as paths FROM (
SELECT guid,
( SELECT code + ' , ' FROM TempTable_Base WHERE guid = A.guid ORDER BY ID FOR XML PATH( '' )) AS UserList
FROM TempTable_Base A
GROUP BY guid
) B
![](http://www.jzxue.com/System/uploads/allimg/101020/2220101.jpg)
(图
3)
3
. 上面的
SQL语句的意思是:
假设以
guid为91E92DCB-141A-30B2-E6CD-B59EABD21749为例,那么guid=A.guid就是先找出值为
91E92DCB-141A-30B2-E6CD-B59EABD21749的记录,并进行
ORDER
BY ID的排序,拿出了这
5行记录以逗号的形式生成链式字符串(
FOR
XML PATH(''))。