SQLServer中利用XML实现行转列

SELECT country
INTO #example
FROM (
  SELECT N'Chinese,Japan,English,Russia' AS country
  Union
  SELECT N'Chinese,Korea' AS country
) x

SELECT DISTINCT country
FROM (
  SELECT CAST('<v>'+REPLACE(country, ',' , '</v><v>') + '</v>' AS xml) AS countrys
  FROM #example
) a
OUTER APPLY (
  SELECT T.C.value('.','VARCHAR(100)') AS country
  FROM a.countrys.nodes('/v') AS T(C)
) b