sql列转行逗号连接_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)[email protected]+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‘,‘#‘)

**/

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

时间： 09-18