SQL Server : split CSV

Question

I have a function to split CSVs up. I have data like this:

identifier  values   
123         abc,def,ghi
124         jre,ds,qwert

And I want the output to be:

123 abc
123 def
123 ghi
124 jre
124 ds
124 qwert

How do I go about doing this?

Thanks, Harry

This is what I currently have;

 SELECT CustomRecordsetId, LTRIM(Value) AS my_values
 FROM
  (
   SELECT *,
   CAST('<X>'+replace(Value,',','</X><X>')+'</X>' as XML) as my_Xml 
   FROM dbo.CustomFieldValue where CustomFieldId=177
  ) T1
  CROSS APPLY
  ( 
  SELECT my_Data.D.value('.','varchar(50)') as my_Splits
  FROM T1.my_Xml.nodes('X') as my_Data(D)
  ) T2

Although this is returning;

id  |       values
596 |   151, 313
596 |   151, 313
603 |   165
604 |   187
605 |   174
606 |   181, 182
606 |   181, 182

I'm trying the first link as we speak. I'm not very good with mssql commands (well, db in general except for nosql). Zane: I don't, it's a horrible practice. I'm doing a database export from a horrid piece of software where I have to do a lookup, a lookup, a join and then another lookup, just to get a related field (when they could just be next to each other in adjacent columns). Unfortunately, I no longer have what I did, since I was just replacing the code. In future, I will keep what I have done though. — Harry Torry, Nov 21 '13 at 15:00

score 2 · Accepted Answer · answered Nov 21 '13 at 14:51

2

I have tried a number of solutions, but this is the one that works best IMO.

 SELECT identifier, LTRIM(T2.my_Splits) AS my_values
 FROM
  (
   SELECT *,
   CAST('<X>'+replace(T.values,',','</X><X>')+'</X>' as XML) as my_Xml 
   FROM YOURTABLE T
  ) T1
  CROSS APPLY
  ( 
  SELECT my_Data.D.value('.','varchar(50)') as my_Splits
  FROM T1.my_Xml.nodes('X') as my_Data(D)
  ) T2

answered Nov 21 '13 at 14:51

smoore4

4,520
3
36
55

I have tried this code now, and amended my original post. Any tips? Thanks for the help. – Harry Torry Nov 21 '13 at 15:10
Never mind, I was meant to use my_Splits and not my_values, I don't know why that didn't occur to me.. – Harry Torry Nov 21 '13 at 15:28
I can't really take credit for this answer. It comes from this SO question, but I didn't have the time to look it up before. And the solution is not the accepted answer. Still KAF is the real author of this genius little snippet. http://stackoverflow.com/questions/13159526/split-values-over-multiple-rows – smoore4 Nov 21 '13 at 17:39
I've hardly used SO except for lurking, is there a way to credit him for it? (or is it just a case of giving him an up vote?). I didn't even realize that you could use XML.. – Harry Torry Nov 22 '13 at 09:43

SQL Server : split CSV

1 Answers1