分别使用函数及游标实现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
declare @str varchar ( 8000 )
set @str = ''
select @str = @str + ' , ' + docName from Doc where UserID = @userID
if ( len ( @str ) > 1 )
set @str =right ( @str , len ( @str ) - 1 )
return ( @str ) 则相应的查询语句为
实现功能,这里仅以很简单的示例,演示了将多行转换成一列的需求,实际中针对docs的结果可能更复杂一些。
需要相应的变通!
第二种方式可以使用游标,这对于很多不理解和不会应用游标的朋友来说,可以学习一下!
运行结果如下:
也达到预期,在这里使用了一条left join查询
在方法二中使用游标在这个结果表中循环。中间使用一个变量结果表@t将结果存入@t中,最后@t即为所需结果
示例表描述如下
一张用户表及文档表
![](https://i-blog.csdnimg.cn/blog_migrate/4f65256aaa1e3a79484455f9223cbdcf.jpeg)
现在想实现如下功能,查询出每个用户及其对应的文档名称的综合,如下结果
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
declare @str varchar ( 8000 )
set @str = ''
select @str = @str + ' , ' + docName from Doc where UserID = @userID
if ( len ( @str ) > 1 )
set @str =right ( @str , len ( @str ) - 1 )
return ( @str )
select
u.
*
,study.dbo.f_getUserDocsByUserID(u.userID)
as
docs
from
[
user
]
as
u
运行效果
![](https://i-blog.csdnimg.cn/blog_migrate/e6f73adc1ebdc749c8f5535b7e7615bb.jpeg)
实现功能,这里仅以很简单的示例,演示了将多行转换成一列的需求,实际中针对docs的结果可能更复杂一些。
需要相应的变通!
第二种方式可以使用游标,这对于很多不理解和不会应用游标的朋友来说,可以学习一下!
--
定义结果集表变量
DECLARE @t TABLE (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
DECLARE @userID_old int , @userID int , @userName varchar ( 50 ), @oldUserName varchar ( 50 ), @docName varchar ( 50 ), @s varchar ( 100 )
-- 打开游标
OPEN MyCURSOR
FETCH MyCURSOR INTO @userID , @userName , @docName
SELECT @userID_old = @userID , @s = '' , @oldUserName = @userName
WHILE @@FETCH_STATUS = 0 -- 游标存在数据时循环
BEGIN
IF @userID = @userID_old -- 如果当前记录和上一条用户ID相同,则更改变量@s的值
SELECT @s = @s + ' , ' + @docName
ELSE
BEGIN -- 否则 已经是下一位用户了,应该执行添加操作
-- UserID改变的时候添加操作
INSERT @t VALUES ( @userID_old , @oldUserName , STUFF ( @s , 1 , 1 , '' ))
-- 将新的文件名赋值给@s 并且更新@userID_old,@oldUserName
SELECT @s = ' , ' + @docName , @userID_old = @userID , @oldUserName = @userName
END
FETCH MyCURSOR INTO @userID , @userName , @docName
END
INSERT @t VALUES ( @userID_old , @oldUserName , STUFF ( @s , 1 , 1 , '' ))
CLOSE MyCURSOR
DEALLOCATE MyCURSOR
-- 显示结果
SELECT * FROM @t
DECLARE @t TABLE (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
DECLARE @userID_old int , @userID int , @userName varchar ( 50 ), @oldUserName varchar ( 50 ), @docName varchar ( 50 ), @s varchar ( 100 )
-- 打开游标
OPEN MyCURSOR
FETCH MyCURSOR INTO @userID , @userName , @docName
SELECT @userID_old = @userID , @s = '' , @oldUserName = @userName
WHILE @@FETCH_STATUS = 0 -- 游标存在数据时循环
BEGIN
IF @userID = @userID_old -- 如果当前记录和上一条用户ID相同,则更改变量@s的值
SELECT @s = @s + ' , ' + @docName
ELSE
BEGIN -- 否则 已经是下一位用户了,应该执行添加操作
-- UserID改变的时候添加操作
INSERT @t VALUES ( @userID_old , @oldUserName , STUFF ( @s , 1 , 1 , '' ))
-- 将新的文件名赋值给@s 并且更新@userID_old,@oldUserName
SELECT @s = ' , ' + @docName , @userID_old = @userID , @oldUserName = @userName
END
FETCH MyCURSOR INTO @userID , @userName , @docName
END
INSERT @t VALUES ( @userID_old , @oldUserName , STUFF ( @s , 1 , 1 , '' ))
CLOSE MyCURSOR
DEALLOCATE MyCURSOR
-- 显示结果
SELECT * FROM @t
运行结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/ee456a525661ce58628540442e4c4f00.jpeg)
也达到预期,在这里使用了一条left join查询
SELECT
u.
*
,d.docName
FROM
[
user
]
u
left
join
[
Doc
]
d
on
u.userID
=
d.userID
order
by
u.userID
它的执行结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/bd267e288151f2cc5e595fd365b3fecc.jpeg)
在方法二中使用游标在这个结果表中循环。中间使用一个变量结果表@t将结果存入@t中,最后@t即为所需结果