在数据库中将同一个字段的多个值用逗号分开,并且合并成一列的方法

在数据库中将同一个字段的多个值用逗号分开,并且合并成一列的方法如下:

在数据库中创建一个自定义函数:

  CREATE   FUNCTION   f_catString(@id   uniqueidentifier)  
  RETURNS   NVARCHAR(1000)  
  AS  
        BEGIN  
            DECLARE   @s   NVARCHAR(1000)  
            SET   @s=''  
            SELECT   @s=@s+','+Name  
            FROM StaffArchive
            INNER JOIN NoticeChecker
            ON StaffArchive.StaffID=NoticeChecker.CheckerID
            WHERE   NoticeChecker.CheckNoticeID=@id
            return(stuff(@s,1,1,''))  
        END  
  GO

然后在查询语句中进行该方法的调用：

 

SELECT *
FROM
( 
SELECT CheckNotice.CheckNoticeID AS CNI1,dbo.f_catString(CheckNoticeID) AS CheckerName
FROM CheckNotice) AS CN1
INNER JOIN
(SELECT CheckNotice.CheckNoticeID AS CNI2,CheckNoticeNumber,CreatNoticeTime
,CheckNotice.WorkNo,StaffArchive.[Name],Station.strStationName,ShiftConfig.ShiftName
,CASE CheckNoticeTypeID WHEN 0 THEN '一般违纪' WHEN 2 THEN '严重违纪' ELSE '表扬' END AS CheckName
,AntiDisciplineTypeConfig.AntiDisciplineTypeName
,WorkGroupConfig.WorkGroupName,LaneConfig.LaneName
FROM CheckNotice
INNER JOIN StaffArchive ON CheckNotice.StaffID=StaffArchive.StaffID
INNER JOIN AntiDisciplineTypeConfig ON AntiDisciplineTypeConfig.AntiDisciplineTypeID=CheckNotice.AntiDisciplineTypeID
INNER JOIN Station ON CheckNotice.StationID=Station.StationId
INNER JOIN ShiftConfig ON CheckNotice.ShiftID=ShiftConfig.ShiftID
INNER JOIN LaneConfig ON CheckNotice.LaneRecordID=LaneConfig.LaneRecordID
INNER JOIN WorkGroupConfig ON CheckNotice.WorkGroupRecordID=WorkGroupConfig.[ID]
WHERE CheckNotice.CreatedRecordTime BETWEEN '2008-10-1 00:00:00' AND '2008-10-31 00:00:00'
AND HandleStatus=2
) AS CN2
ON CN1.CNI1=CN2.CNI2
ORDER BY CreatNoticeTime DESC
;