0

I have a table named food having column speciality. And values on rows:

Speciality (column name)
......................................

Chinese, south Indian, Mediterranean, American
......................................

South Indian, fast food, shakes, coldrinks, Chinese

......................................

And I want out as unique values on separate rows like:

South Indian
Chinese
Mediterranean
American
Fast food
Shakes

And save it to another table food2 having column name speciality2, I want to save those values into that column.

All the database in SQL server 2008.

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Nitesh kumar
  • 13
  • 7
  • Many answers on the web refer to [this article](http://www.sqlservercentral.com/articles/Tally+Table/72993/). It seems that you will need to register to get access. – PM 77-1 May 23 '15 at 18:28

1 Answers1

0

I think the split function along with XML can help you try below code

Insert Into Food2(Speciality2)
SELECT   Distinct
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT  
         CAST ('<M>' + REPLACE([Speciality], ',', '</M><M>') + '</M>' AS XML) 
     AS String  
     FROM  food) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
Sachu
  • 7,555
  • 7
  • 55
  • 94