0

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):

'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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rakesh kapri
  • 3
  • 3
  • 1
    See the second answer of the https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows – Low Flying Pelican Nov 24 '17 at 04:41
  • I think that would work if I had these string in multiple columns in a table. But in my case I have a list of external text (csv file) which I need to convert. Not sure if that solution would work in this case. – rakesh kapri Nov 24 '17 at 04:49

1 Answers1

2

Try some xml node method which could help you

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'
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52