单实用SQL脚本Part：sql多行转为一列的合并问题

一、数据库SQL Server列值链式合并

需求：原始表的数据的结构如图1所示，把相同guid的code值生成一个链式字符串。

（图1）

目标：我们希望达到的效果如图2所示，这里的guid变成唯一的了，这行的记录中包含了这个guid所对应的code字段值的链式字符串。

（图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 ]

 

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

 

（图3）

 3.      上面的SQL语句的意思是：

假设以guid为91E92DCB-141A-30B2-E6CD-B59EABD21749为例，那么guid=A.guid就是先找出值为91E92DCB-141A-30B2-E6CD-B59EABD21749的记录，并进行ORDER BYID的排序，拿出了这5行记录以逗号的形式生成链式字符串（FOR XML PATH('')）。