Sql Server某列中内容用特殊字符隔开转多行操作
1 --创建临时表模拟数据 2 CREATE TABLE #temp(id int,姓名 VARCHAR(50),兴趣爱好 VARCHAR(500)) 3 4 --插入模拟数据 5 INSERT INTO #temp(id,姓名,兴趣爱好) 6 SELECT 1 AS id ,'张三' AS 姓名,'爬山、下棋、游泳' 兴趣爱好 7 UNION ALL 8 SELECT 2 AS id,'李四' AS 姓名,'游泳' AS 兴趣爱好 9 UNION ALL 10 SELECT 3 AS id,'王五' AS 姓名,NULL AS 兴趣爱好 11 12 --转换结果 13 ;WITH #tempResult AS 14 ( 15 SELECT 16 A.id,A.姓名,A.兴趣爱好, CHARINDEX('、', A.兴趣爱好) AS charStart, CHARINDEX('、', A.兴趣爱好) - 1 AS charLen 17 FROM #temp A 18 UNION ALL 19 SELECT 20 id,姓名,兴趣爱好, CHARINDEX('、', 兴趣爱好, charStart + 1) AS charStart, CHARINDEX('、', 兴趣爱好, charStart + 1) - charStart - 1 AS charLen 21 FROM #tempResult 22 WHERE (charStart <> 0) 23 ) 24 SELECT id,姓名, CASE WHEN charLen <> -1 THEN SUBSTRING(兴趣爱好, charStart - charLen, ABS(charLen)) ELSE 兴趣爱好 END AS 兴趣爱好 25 FROM #tempResult 26 ORDER BY id ASC
结果展示: