Sql Server 列转逗号隔开的字符串 和 逆转

SELECT  STUFF(( SELECT  ',' + ModifyBy
                FROM    dbo.tbl_stationprofile
              FOR
                XML PATH('')
              ), 1, 1, '') AS Name    

 

 

逗号隔开的字符串转表

USE [MBG3SDB]
GO
/****** Object:  UserDefinedFunction [dbo].[GetIDList]    Script Date: 2017/9/12 10:13:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:    将字符串转换为列表(@InStr：字符串，@sp：分隔符)
-- =============================================
ALTER FUNCTION [dbo].[GetIDList](@InStr VARCHAR(max)='',@sp CHAR(1)=',')
RETURNS @TempTable TABLE ([ID] VARCHAR(50)) 
AS
BEGIN
    IF @InStr IS NULL OR rtrim(ltrim(@InStr))=''
        RETURN
    DECLARE @FirstID VARCHAR(50),@SpIndex int 
    IF(@sp IS NULL OR @sp='')
        SET @sp=','
    WHILE @InStr<>''
    BEGIN
        SET @SpIndex=CHARINDEX(@sp,@InStr)
        IF(@SpIndex=1)
        BEGIN
            SET @InStr=SUBSTRING(@InStr,2,DATALENGTH(@InStr))
            CONTINUE
        END
        ELSE IF(@SpIndex>1)
        BEGIN
            SET @FirstID=SUBSTRING(@InStr,1,@SpIndex-1)
            SET @InStr=SUBSTRING(@InStr,@SpIndex+1,DATALENGTH(@InStr)-@SpIndex+1)
        END
        ELSE
        BEGIN
            SET @FirstID=@InStr
            SET @InStr=''
        END
        INSERT INTO @TempTable SELECT @FirstID
    END
    RETURN
END
/**
示例
select * from dbo.GetIDList('5#6#7','#')
**/

 

自己创建表函数自己试吧。下面就不上图了。