T_SQL 将一列多行数据合并为一行

CREATE FUNCTION FN_Merge (@Student NVARCHAR(50))

RETURNS NVARCHAR(50)

 

AS

      BEGIN

            DECLARE @Course NVARCHAR(50)

            SELECT @Course = ISNULL(@Course + ‘,’,”) + @Course

            FROM SC

            WHERE Student = @Student

            RETURN @COURSE

     END

 

SELECT DISTINCT [Student]

      ,dbo.FN_Merge([Student]) AS Course

FROM [dbo].[SC]

结果：

(2 row(s) affected)

                                   

方法二：FOR XML PATH

SELECT  DISTINCT [Student]

      ,STUFF(

                        (

                              SELECT ‘,’+[Course]

                              FROM [dbo].[SC]

                              WHERE Student = A.Student

                              FOR XML PATH(”)

                        )

                        ,1,1,”

                  )AS Course

  FROM [dbo].[SC] AS A

结果：

(2 row(s) affected)

 

 

链接：

1. Stuff（）函数http://msdn.microsoft.com/zh-cn/library/ms188043.asp

转载：http://www.cnblogs.com/smalleyes/archive/2012/03/08/2385658.html

 

---

以上就是T_SQL 将一列多行数据合并为一行的全部内容了，更多内容请关注：CPP学习网_CPP大学
本文固定链接：CPP学习网_CPP大学-T_SQL 将一列多行数据合并为一行

T_SQL 将一列多行数据合并为一行

标签：begin   info   height   add   www   alt   object_id   zh-cn   nvarchar