How to break comma separated value into column

Question

Possible Duplicate:
SQL 2005 Split Comma Separated Column on Delimiter

Specialist       JID
--------------- -------------------
ANA             1 ,5 ,9 ,7 ,6
GEN             2 ,3 ,11 ,13 ,45 ,23 ,28 ,41 ,49 ,52
robin           12 ,15 ,17

suppose my query is returning the above output. how can i break the comma separated data into separated columns. is it possible....please guide. thanks

This could help http://stackoverflow.com/questions/2647/split-string-in-sql — Algorithmist, Jun 03 '11 at 13:32
http://www.codeproject.com/KB/database/SQL_UDF_to_Parse_a_String.aspx — Algorithmist, Jun 03 '11 at 13:32

score 1 · Answer 1 · answered Jun 03 '11 at 13:54

If you want a different number of columns for each record depending on how many there are no it is not possible.

If you want to break them out that is very painful to do and will kill performance in a large dataset. The only correct action is to fix the poor design and store this data in a child table as it should be stored. There is virtually no circumstance where it is a good idea to to store a comma delimted list in one field.

How to break comma separated value into column

1 Answers1