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

[转载]简单实用SQL脚本Part:sql多行转为一列的合并问题 – 我帅故我在 – 博客园.

一、数据库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 (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 Server2005FOR 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语句的意思是:

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

——————-华丽分割线——————-

作者:听风吹雨
版权:本文版权归作者和博客园共有
转载:欢迎转载,不过记得留下买路钱
格言:不喜欢是因为你不会 && 因为会所以喜欢
赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏