代码改变世界

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

2010-10-20 09:41  听风吹雨  阅读(6702)  评论(14编辑  收藏

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

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

(图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 (11)  NOT NULL,
[guid] [varchar]  (50NULL,
[code] [varchar]  (50NULL)

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 Server2005FOR XML PATH把记录数据以XML的格式组织起来,把同一个guid的数据进行字符串的拼凑。执行下面的SQL就可以达到图3所示的效果了。 

--列值链式合并
SELECT  B.guid,LEFT(UserList,LEN(UserList)-1as 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语句的意思是:

假设以guid91E92DCB-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得到验证