【sqlserver】带分隔符字符串和多行互相转化

 SELECT b.T 
FROM(
  SELECT CONVERT(XML,'<n>'+REPLACE(REPLACE('HY-107860;HY-107871;HY-109816;HY-109824;HY-109826;HY-113868;HY-2169',';',','),',','</n><n>')+'</n>') AS xmlval 
) a 
CROSS APPLY(
  SELECT k.n.value('.','nvarchar(80)') T 
  FROM a.xmlval.nodes('n') k(n)
) b  

  SELECT releaseId, sprojectId 
  FROM (SELECT releaseId, projectId=cast('<v>'+replace(projectId,'^','</v><v>')+'</v>' as xml) FROM ReleaseInfo) a outer apply (SELECT sprojectId=T.C.value('.','nvarchar(50)') from a.projectid.nodes('/v') as T(C) )b
  
    where a.releaseId='FASRPT_V1.0.0.4'

WITH tmp AS ( SELECT releaseId, projectId FROM ReleaseInfo  ) SELECT
releaseId,
projectId,
stuff(( SELECT DISTINCT ',' + projectId FROM tmp WHERE releaseId = t.releaseId FOR xml path ( '' )), 1, 1, '' ) AS projectIds 
FROM
  tmp t
    
  where t.releaseId='FASRPT_V1.0.0.4'