【无标题】

将上图转换为下图

第一步：创建函数，按字符拆分字符串

CREATE FUNCTION [dbo].[UF_SplitStringToTable](
    @sInputList VARCHAR(MAX) -- List of delimited items
  , @sDelimiter VARCHAR(20) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(50))

BEGIN
DECLARE @sItem VARCHAR(MAX)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

第二步：应用OUTER APPLY和函数实现目标

SELECT 
   t1.[Id]
  ,t1.[Keywords]
  ,t1.[FileName]
  ,t1.[FileExtension]
  ,t1.[CreatedUser]
  ,t1.[CreatedTime]
  ,t1.[ImportType] 
  ,i.item as keyword  
FROM FileHistory t1
	OUTER APPLY UF_SplitStringToTable(t1.Keywords, ',') i

-- 若使用函数默认值，则将最后一行改为OUTER APPLY UF_SplitStringToTable(t1.Keywords, Default) i

文章转自：https://www.cnblogs.com/L-may/p/4503367.html