转】 简单实用SQL脚本：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 BY ID的排序，拿出了这5行记录以逗号的形式生成链式字符串(FOR XML PATH(''))。

　　二、参考文献

　　FOR XML PATH 语句的应用

　　sql多行转为一列的合并问题，并在sql2000和2005得到验证