SQL行转成以逗号分割的字符串！

1、首先是学生表中有ClassId这一列，最终先展示效果大至如：12,13,14的结果，底下有两种做法可参考：

•   利用sql语言 xml path做

select parentId=(select ParentId from Organization where Id=586),
         ClassId=isnull
         (
           (
               select temp.ClassId from
               ( 
                  select ClassId=stuff
                  (
	     (
                       select ','+Convert(nvarchar(10),ClassId,120) from Class
                               where OgId=586 and Grade=2011
	               for xml path('')
	      ), 1, 1, ''
	  )  from Class where OgId=586 and Grade=2011
              ) temp group by temp.ClassId
           ),''
         )

 

•   利用函数去做

 

/****** 对象:  UserDefinedFunction [dbo].[GetClassIdByStuId]    脚本日期: 04/14/2012 19:08:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[GetClassIdByStuId]
(@StuID INT)
--实现spilt功能 的函数
--date    :2003-10-14
returns nvarchar(4000) 
AS
begin
    DECLARE @temp VARCHAR(4000)
    SET @temp=''
    SELECT @temp =(
                    CASE WHEN @temp =''
                        THEN
                          CAST(ClassId as varchar)
                        ELSE
                          @temp+','+ CAST(ClassId as varchar)
                        END
                   )
    FROM ClassStuInfo WHERE ClassStuInfo.StuId=@StuID
    RETURN @temp
end

 上面写的很清楚，希望对大家有用！

转载于:https://www.cnblogs.com/BeyondWJsel/archive/2012/04/21/2461114.html