SQL Server中多行多列连接成为单行单列

时间:2021-12-19 02:34:49

CREATE FUNCTION ConvertVertToHorz(@UserId int)

RETURNS VARCHAR(8000)

AS

BEGIN

-- 实际项目中,应该考虑 @RetVal 是否会超过 8000 个字符

DECLARE @RetVal varchar(8000)

SET @RetVal = ''

-- 通过递归 SELECT 连接指定列存储到临时变量中

SELECT @RetVal = (cast(Id as varchar) + ',' + @RetVal) FROM YourTab WHERE UserId = @UserId

-- 连接多列

-- SELECT @RetVal = Column2 + ',' + Column3 + ',' + Column4 + ',' + @RetVal FROM VertToHorzSample WHERE Column1 = @Col1Val

-- 去掉尾巴的 , (逗号)

IF LEN(@RetVal) > 0

SET @RetVal = LEFT(@RetVal, LEN(@RetVal) - 1)

--PRINT @RetVal


RETURN @RetVal

END

GO