splitting a single row into multi rows [duplicate]

This question already has an answer here: Closed 11 years ago.

Possible Duplicate:

query to display a row data as column

hi i have a single row holding data as 1,2,10,4,5,6,7,8,13,16,17,3. I need a query to split them into sequence row. such that 1 should appear in 1st row, 2 in second row, 10 in third row...........开发者_开发问答.....

---

Use replace to make your comma separated string into a xml. Then you can use cross apply to get the nodes() of the xml as rows and use the value() function to get the node value.

declare @Str varchar(100) = '1,2,10,4,5,6,7,8,13,16,17,3'

select 
  r.value('.', 'int') as Val
from (select cast('<r>'+replace(@Str, ',', '</r><r>')+'</r>' as xml)) as x(x)
  cross apply
    x.nodes('r') as r(r)

If you need to use this for a table instead of a variable you can have a look here. How to split repeating string delimated by commas in T-SQL