分别使用函数及游标实现SQL多行转一列
有时候在一些系统中我们需要用SQL语句将一对多关系表中的数据以每条记录一行的形式进行显示。本文分别采用函数及游标的方法来实现该功能。
示例表描述如下
一张用户表及文档表
现在想实现如下功能，查询出每个用户及其对应的文档名称的综合，如下结果
userID??????????????????????????? userName?????????????????????????????? DocNames
1????????????????????????????????????fengyan??????????????????????????? sql基础，asp.net入门
2????????????????????????????????????eflylab???????????????????????????????????? 精品美食
注意到最后一列是类似统计。这就决定不能使用left join做，那样1号用户会出现二行，不是期望的
其实很多时间需求是需要我们使用SQL完成这样的功能，而不是放在程序里！如果做？
这里可以写一个函数来完成该功能
CREATE?function?f_getUserDocsByUserID(@userID?int)?
returns?varchar(8000)?
as?
begin?
[email protected](8000)?
[email protected][email protected]+',[email protected]
if(len(@str)>1)
[email protected]=right(@str,len(@str)-1)?
return(@str)?
则相应的查询语句为
select?u.*,study.dbo.f_getUserDocsByUserID(u.userID)?as?docs?from?[user]?as?u
运行效果
实现功能，这里仅以很简单的示例，演示了将多行转换成一列的需求，实际中针对docs的结果可能更复杂一些。
需要相应的变通！
第二种方式可以使用游标，这对于很多不理解和不会应用游标的朋友来说，可以学习一下！
--定义结果集表变量
[email protected](userID?varchar(10),userName?varchar(100),DocNames?varchar(8000))
--定义游标并进行合并处理
DECLARE?MyCURSOR?CURSOR??
FOR
SELECT?u.*,d.docName?FROM?[user]?u?left?join?[Doc]?d?on?u.userID=d.userID??order?by?u.userID
[email protected]_old?int,@userID?int,@userName?varchar(50),@oldUserName?varchar(50),@docName?varchar(50),@s?varchar(100)
--打开游标
OPEN?MyCURSOR
[email protected],@userName,@docName
[email protected][email protected],@s='',@[email protected]
WHILE?@@FETCH_STATUS=0????--游标存在数据时循环
BEGIN
[email protected][email protected]_old????--如果当前记录和上一条用户ID相同，[email protected]
[email protected][email protected]+',[email protected]
????ELSE
????BEGIN????--否则?已经是下一位用户了，应该执行添加操作
????????--UserID改变的时候添加操作
[email protected](@userID_old,@oldUserName,STUFF(@s,1,1,''))
[email protected]@userID_old,@oldUserName
[email protected]=',[email protected],@[email protected],@[email protected]
????END
[email protected],@userName,@docName
END
[email protected](@userID_old,@oldUserName,STUFF(@s,1,1,''))
CLOSE?MyCURSOR
DEALLOCATE?MyCURSOR
--显示结果
运行结果如下：
也达到预期，在这里使用了一条left join查询
SELECT?u.*,d.docName?FROM?[user]?u?left?join?[Doc]?d?on?u.userID=d.userID??order?by?u.userID
它的执行结果如下：
在方法二中使用游标在这个结果表中循环。中间使[email protected]@t中，[email protected]
?
?
?
?
?
喜欢就来看看.................