在MS SQL中将表中多行数据合并成一列数据

http://jackyxfl.blog.163.com/blog/static/164134150201111744558743/

 

假设有一个文章表 Post 和一个评论表 Comments，可以对文章进行多次评论，现在希望在对 Post 表查询时能将 Post 的所有评论内容组合到一个字段中显示。

--首先创建表Post和Comments
CREATE TABLE Post
(
	[PostID] INT IDENTITY(1, 1) PRIMARY KEY NOT NULL ,
	[Title] NVARCHAR(50) ,
	[Content] TEXT ,
	[CreateDate] DATETIME DEFAULT GETDATE()
)
CREATE TABLE Comments
(
	[CommentID] INT IDENTITY(1, 1) PRIMARY KEY NOT NULL ,
	[PostID] INT ,
	[Content] TEXT ,
	[CreateDate] DATETIME DEFAULT GETDATE()
)


--给这两个表添加一些测试数据
INSERT INTO POST SELECT '来看过猫博吗？','你肯定看过',GETDATE()
INSERT INTO COMMENTS SELECT 1,'绝对看过猫博',GETDATE()
INSERT INTO COMMENTS SELECT 1,'必须看过猫博',GETDATE()
INSERT INTO COMMENTS SELECT 1,'谁没看过猫博呢？',GETDATE()


--评论内容的组合使用一个函数来实现，在函数中使用游标去遍历给定PostID的所有评论然后进行拼接，企业网站源码下载函数代码如下：
CREATE  FUNCTION fn_GetAllComments ( @PostID INT )
RETURNS NVARCHAR(4000)
AS 
    BEGIN
        DECLARE @result VARCHAR(4000)
        SET @result = ''
        DECLARE getAllComments CURSOR
        FOR
        SELECT CommentID FROM Comments WHERE PostID=@PostID
        OPEN getAllComments
        DECLARE @ID SYSNAME
        FETCH  FROM getAllComments INTO @ID
        WHILE @@fetch_status = 0 
            BEGIN
                SET @result = @result
                    + ( SELECT  CONVERT(NVARCHAR(20), CreateDate, 120)
                        FROM    Comments
                        WHERE   CommentID = @ID
                      ) + ':' + ( SELECT    CAST([Content] AS NVARCHAR(4000))
                                  FROM      Comments
                                  WHERE     CommentID = @ID
                                ) + '；'
                FETCH  FROM getAllComments INTO @ID
            END
        CLOSE getAllComments
        SET @result = SUBSTRING(@result, 0, LEN(@result))
        DEALLOCATE getAllComments
        RETURN @result
    END


--现在写SQL语句来测试一下结果
SELECT  Title ,
        [Content] ,
        CreateDate ,
        dbo.fn_GetAllComments(PostID) AS AllComments
FROM    Post

 
 

  
  
 
 
 或者使用以下语句:

declare @result varchar(500)
set @result=''
select @result=@result+pribatchname+',' from dbo.tbl_Batches where createdatetime>'2011-02-18 00:00:00.000'
select @result as 合并结果