SQL分割字符串成多行

直接上图

 原始数据为分号分隔。

  SELECT b.vv as planClass FROM( 
  SELECT CONVERT(XML,'<n>'+REPLACE(REPLACE([plan_class],';',','),',','</n><n>')+'</n>') AS xmlval  
  FROM [VWPFCC_BDE].[dbo].[t_bd_scheduling]   
  WHERE [line_id] ='4a2e1ae6-4f4f-4f4b-b1b6-3d69b70caf59') a 
  CROSS APPLY(SELECT k.n.value('.','nvarchar(80)') vv FROM a.xmlval.nodes('n') k(n)) b  

查询后结果如下

 

里面有空数据，需要处理下

SELECT
 T_A.planClass
 FROM (
  SELECT b.vv as planClass FROM( 
  SELECT CONVERT(XML,'<n>'+REPLACE(REPLACE([plan_class],';',','),',','</n><n>')+'</n>') AS xmlval  
  FROM [VWPFCC_BDE].[dbo].[t_bd_scheduling]   
  WHERE [line_id] ='4a2e1ae6-4f4f-4f4b-b1b6-3d69b70caf59') a 
  CROSS APPLY(SELECT k.n.value('.','nvarchar(80)') vv FROM a.xmlval.nodes('n') k(n)) b  
  ) T_A
  WHERE T_A.planClass !=''

处理后如下