|
http://www.itfarmer.com.cn/878.html
SQLServer查询多行转换为一行的合并问题(FOR XML PATH应用)
以下主讲sql多行转为一行的合并问题，并在sql2000和2005得到验证
问题描述：将如下形式的数据按id字段合并value字段。
id??? value
—– ——
1???? aa
1???? bb
2???? aaa
2???? bbb
2???? ccc
需要得到结果：
id???? value
—— ———–
1????? aa,bb
2????? aaa,bbb,ccc
即：group by id,求 value 的和（字符串相加）
定义表：
create table tb(id int,value varchar(10))
insert into tb values(1,‘aa’)
insert into tb values(1,‘bb’)
insert into tb values(2,‘aaa’)
insert into tb values(2,‘bbb’)
insert into tb values(2,‘ccc’)
go
一、sql2000中要用自定义的函数解决
create function dbo.f_str(@id int) returns varchar(100)
as
begin
??? declare @str varchar(1000)
??? set @str = ”
??? select @str = @str + ‘,’ + cast(value as varchar) from tb where id = @id
??? set @str = right(@str,len(@str) – 1)
??? return @str
end
go
–调用函数
select id,value = dbo.f_str(id) from tb group by id
–drop function dbo.f_str
–drop table tb
二、sql2005中可以用FOR XML PATH语句来实现
大家都知道在SQL Server中利用 FOR XML PATH 语句能够把查询的数据生成XML数据，下面是它的一些应用示例。
DECLARE @TempTable table(UserID int,UserName nvarchar(50));
insert into @TempTable (UserID,UserName) values (1,’a’)
insert into @TempTable (UserID,UserName) values (2,’b’)
select UserID,UserName from @TempTable FOR XML PATH
运行这段脚本，将生成如下结果：
<row>
? <UserID>1</UserID>
? <UserName>a</UserName>
</row>
<row>
? <UserID>2</UserID>
? <UserName>b</UserName>
</row>
可以看到两行数据生成了两个节点，修改一下PATH的参数：
select UserID,UserName from @TempTable FOR XML PATH(‘test’)
运行上述脚本，将生成如下的结果：
<test>
? <UserID>1</UserID>
? <UserName>a</UserName>
</test>
<test>
? <UserID>2</UserID>
? <UserName>b</UserName>
</test>
其实PATH() 括号内的参数是控制节点名称的，那如果是空字符串（不是没有参数）会是什么结果呢?
select UserID,UserName from @TempTable FOR XML PATH(”)
执行上面这段脚本将生成结果：
<UserID>1</UserID>
<UserName>a</UserName>
<UserID>2</UserID>
<UserName>b</UserName>
这样就不显示上级节点了，在PATH模式中，列名或列别名被作为XPath表达式来处理，也就是说，是列的名字，试一下不给指定列名和别名会是怎么样？
select CAST(UserID AS varchar) + ”,UserName + ” from @TempTable FOR XML PATH(”)
运行上面这句将生成结果
1a2b
所有数据都生成一行，而且没有连接字符，还可以再变化一下：
select CAST(UserID AS varchar) + ‘,’,UserName + ”,';’ from @TempTable FOR XML PATH(”)
生成结果
1,a;2,b;
也就是说可以通过控制参数来生成自己想要的结果，例如：
select ‘{‘ + CAST(UserID AS varchar) + ‘,'”‘ +UserName + ‘”‘,’}’ from @TempTable FOR XML PATH(”)
生成结果
{1,”a”}{2,”b”}
还可以生成其他格式，大家可以根据自己需要的格式进行组合。
下面是一个数据统计的应用，希望大家可以通过下面的实例想到更多的应用
DECLARE @T1 table(UserID int,UserName nvarchar(50),CityName nvarchar(50));
insert into @T1 (UserID,UserName,CityName) values (1,’a’,’上海’)
insert into @T1 (UserID,CityName) values (2,’b’,’北京’)
insert into @T1 (UserID,CityName) values (3,’c’,CityName) values (4,’d’,CityName) values (5,’e’,’上海’)
SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) FROM (
SELECT CityName,
??? (SELECT UserName+’,’ FROM @T1 WHERE CityName=A.CityName? FOR XML PATH(”)) AS UserList
FROM @T1 A
GROUP BY CityName
) B
生成结果（每个城市的用户名）
北京 b,d
上海 a,c,e
回到上面的例子就是执行如下语句：
select id,[value] = stuff((select ‘,’ + [value] from tb t where id = tb.id for xml path(”)),1,”)
from tb
group by id
–drop table tb
（STUFF函数用法
1、作用：删除指定长度的字符，并在指定的起点处插入另一组字符。
2、语法：STUFF ( character_expression,start,length,character_expression )
3、示例：在第一个字符串abcdef中删除从第2个位置（字符b）开始的3个字符，然后在删除的起始位置插入第二个字符串ijklmn，从而创建并返回一个新字符串
SELECT STUFF(‘abcdef’,2,3,‘ijklmn’)
GO
下面是结果集：aijklmnef
4、参数：character_expression，一个字符数据表达式。character_expression可以是常量、变量，也可以是字符列或二进制数据列。
start，一个整数值，指定删除和插入的开始位置。如果start或length为负，则返回空字符串。如果start比第一个character_expression长，则返回空字符串。start可以是bigint类型。
length，一个整数，指定要删除的字符数。如果length比第一个character_expression长，则最多删除到最后一个 character_expression中的最后一个字符。length可以是bigint类型。
5、返回类型：如果 character_expression 是受支持的字符数据类型，则返回字符数据。如果 character_expression 是一个受支持的 binary 数据类型，则返回二进制数据。
6、备注：如果结果值大于返回类型支持的最大值，则产生错误。）
三、使用游标来合并数据，这个方法无版本限制
declare @t table(id int,value varchar(100))–定义结果集表变量
–定义游标并进行合并处理
declare my_cursor cursor local for
select id,value from tb
declare @id_old int,@id int,@value varchar(10),@s varchar(100)
open my_cursor
fetch my_cursor into @id,@value
select @id_old = @id,@s=”
while @@FETCH_STATUS = 0
begin
??? if @id = @id_old
?????? select @s = @s + ‘,’ + cast(@value as varchar)
??? else
????? begin
??????? insert @t values(@id_old,stuff(@s,”))
??????? select @s = ‘,’ + cast(@value as varchar),@id_old = @id
????? end
??? fetch my_cursor into @id,@value
END
insert @t values(@id_old,”))
close my_cursor
deallocate my_cursor
select * from @t
–drop table tb
这个一般常见用于数据统计，平常用的不多。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
