废话不多说,直接来贴代码,被这问题困扰了一天,后来网上看到别人写的给了我灵感,自己改造了一个更容易理解的方法。希望对大家有所帮助。(最后悄悄告诉大家我发现的一个新大陆,group by 可以去重)
亲测有效哦
USE XXX
--将符号拼接的字段数据转换成table
if exists(select * from sysobjects where id=OBJECT_ID('F3_SplitStr'))
drop function F3_SplitStr
GO
Create FUNCTION [dbo].[F3_SplitStr]
(
@SplitValue VARCHAR(MAX),
@Separate VARCHAR(100)
)
RETURNS @ReturnTable TABLE(RealValue VARCHAR(MAX))
AS
BEGIN
DECLARE @SplitStr VARCHAR(MAX)
DECLARE @SepIndex BIGINT
DECLARE @LastSepIndex BIGINT
/* 当分隔符长度为0,直接返回包含一条记录,且内容为@SplitValue的表结果 */
IF LEN(@Separate)=0
BEGIN
INSERT INTO @ReturnTable Values(@SplitValue)
RETURN
END
SET @LastSepIndex = 0
IF Right(@SplitValue ,len(@Separate)) <> @Separate
BEGIN
SET @SplitValue = @SplitValue+@Separate
END
SET @SepIndex = CharIndex(@Separate,@SplitValue ,@LastSepIndex)
WHILE @LastSepIndex <= @SepIndex
BEGIN
SET @SplitStr = SubString(@SplitValue,@LastSepIndex,@SepIndex-@LastSepIndex)
SET @LastSepIndex = @SepIndex + LEN(@Separate)
SET @SepIndex = CharIndex(@Separate,@SplitValue,@LastSepIndex)
INSERT INTO @ReturnTable Values(@SplitStr)
END
RETURN
END
USE XXX
GO
DECLARE @TableInfo TABLE(Numbers INT ,Name VARCHAR(MAX))
INSERT INTO @TableInfo (Numbers,Name) VALUES('1','张三,李四,王五,赵柳,王五')
INSERT INTO @TableInfo (Numbers,Name) VALUES('2','allie,lisa,jonne,lily,lily')
--输出处理前的表
SELECT * FROM @TableInfo
--整理数据,给需要的数据加序号,以便于后面循环修改数据
DECLARE @TableInfos TABLE(RowNumber INT,Numbers INT ,Name VARCHAR(MAX))
INSERT INTO @TableInfos
SELECT ROW_NUMBER() OVER(ORDER BY Numbers), * FROM @TableInfo
--2.循环更新每一行
DECLARE @i INT,@iMax INT,@Name VARCHAR(MAX)
SELECT @i= ISNULL(MIN(RowNumber),1) ,@iMax = ISNULL(max(RowNumber),0) FROM @TableInfos
WHILE @i<=@iMax
BEGIN
SELECT @Name=Name FROM @TableInfos WHERE RowNumber = @i
UPDATE @TableInfos SET Name=
(SELECT STUFF(
(
SELECT ','+ RealValue FROM F3_SplitStr(@Name,',') GROUP BY RealValue
FOR XML PATH('')
),1,1,''))
WHERE RowNumber =@i
SET @i=@i+1
END
--输出处理过的表
SELECT * FROM @TableInfos