使用函数及游标实现SQL多行转一列

分别使用函数及游标实现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) 

则相应的查询语句为

select u.*,study.dbo.f_getUserDocsByUserID(u.userID) as docs from [user] as u

运行效果

实现功能，这里仅以很简单的示例，演示了将多行转换成一列的需求，实际中针对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

运行结果如下：

也达到预期，在这里使用了一条left join查询

SELECT u.*,d.docName FROM [user] u left join [Doc] d on u.userID=d.userID  order by u.userID

它的执行结果如下：

在方法二中使用游标在这个结果表中循环。中间使用一个变量结果表@t将结果存入@t中，最后@t即为所需结果

 

 

 

 

 

喜欢就来看看.................