Sql Server某列中内容用特殊字符隔开转多行操作

--创建临时表模拟数据
CREATE TABLE #temp(id int,姓名 VARCHAR(50),兴趣爱好 VARCHAR(500))

--插入模拟数据
INSERT INTO #temp(id,姓名,兴趣爱好)
SELECT 1 AS id ,'张三' AS 姓名,'爬山、下棋、游泳' 兴趣爱好
UNION ALL
SELECT 2 AS id,'李四' AS 姓名,'游泳' AS 兴趣爱好
UNION ALL
SELECT 3 AS id,'王五' AS 姓名,NULL AS 兴趣爱好

--转换结果
;WITH #tempResult AS 
(
　　SELECT 
　　　　A.id,A.姓名,A.兴趣爱好, CHARINDEX('、', A.兴趣爱好) AS charStart, CHARINDEX('、', A.兴趣爱好) - 1 AS charLen
　　FROM #temp A
　　UNION ALL
　　SELECT 
　　　　id,姓名,兴趣爱好, CHARINDEX('、', 兴趣爱好, charStart + 1) AS charStart, CHARINDEX('、', 兴趣爱好, charStart + 1) - charStart - 1 AS charLen
　　FROM #tempResult 
　　WHERE (charStart <> 0)
)
SELECT id,姓名, CASE WHEN charLen <> -1 THEN SUBSTRING(兴趣爱好, charStart - charLen, ABS(charLen)) ELSE 兴趣爱好 END AS 兴趣爱好
FROM #tempResult 
ORDER BY id ASC

结果展示：