使用SQL Server将逗号分隔文本转换为多列结果

I have a requirement wherein I need to convert a long list of text (separated by comma) into multiple row result-set.

我有一个要求,我需要将一长串文本(用逗号分隔)转换为多行结果集。

This would then be used in an outer join with some other table.

然后,这将在与其他表的外连接中使用。

Problem string (which is not in a table but an external string in an excel sheet):

问题字符串(不在表中,而是excel表中的外部字符串):

'A', 'B', 'C', ....

Expected output of query

预期的查询输出

Output
-----
'A'  
'B'  
'C'  

I am trying to avoid using temporary tables while achieving above solution.

我试图避免使用临时表,同时实现上述解决方案。

Kindly suggest if it is possible.

请提出是否有可能。

1 个解决方案

#1

Try some xml node method which could help you

尝试一些可以帮助你的xml节点方法

DECLARE @CommValues NVARCHAR(MAX) = '''A'', ''B'', ''C'''


SELECT 
      LTRIM(A.value('.', 'NVARCHAR(MAX)')) [Comma Values] FROM 
(
    SELECT CAST('<X>'+REPLACE(@CommValues, ',', '</X><X>')+'</X>' AS XML) AS Comm
) X CROSS APPLY Comm.nodes('/X') as comm(a)

Result :

Comma Values
'A'
'B'
'C'