How to break comma separated value into column [duplicate]

This question already has answers here: 开发者_如何学C Closed 11 years ago.

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

---

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.