Sql query to create single row to multiple row

Question

I have a table as given below

ID           Value
-----        ----------
1            10,20,30

I need the result as

ID         Value
---        -------
1           10
1           20
1           30

Thanks.

Why have you stored `10,20,30` at all? Isn't it possible to store it already as you want it in the result? So you should create a new table with a foreign-key to the first table. That process is called [normalization](http://en.wikipedia.org/wiki/Database_normalization). — Tim Schmelter, Sep 04 '14 at 13:10
I know you may think you are earning space in the way you are storing them, but you are going to lose processing time displaying them. I agree with @Tim , have separate rows. Even have another table for One-Many or Many-Many relationship. This is why we have Relational DBs. — Giannis Paraskevopoulos, Sep 04 '14 at 13:14
@user3433179: yes, there are already many duplicates, f.e. http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns or http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco or http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x or http://stackoverflow.com/questions/6392340/sql-server-2008-split-multi-value-column-into-rows-with-unique-values .But instead of splitting the string i would really change the model. — Tim Schmelter, Sep 04 '14 at 13:18
100% agree that you should change your model and use the relational database in the manor it was intended. However, everything you need to know, and probably more is covered in this article by Aaron Bertrand: **[Split strings the right way – or the next best way](http://sqlperformance.com/2012/07/t-sql-queries/split-strings)**. I don't believe there is anything I could add to this in an answer so I won't even try. — GarethD, Sep 04 '14 at 13:24

score 0 · Answer 1 · edited May 23 '17 at 12:21

This will work

  ;with tmp(Id, value, Data) as (
select Id,  LEFT(value, CHARINDEX(',',value+',')-1),
    STUFF(value, 1, CHARINDEX(',',value+','), '')
from @Testdata
union all
select Id, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select Id,  value
from tmp
order by Id

Possible duplicate Turning a Comma Separated string into individual rows

score 0 · Answer 2 · answered Sep 04 '14 at 13:33

Please try the following:

 SELECT A.ID,  
        Split.a.value('.', 'VARCHAR(100)') AS Value  
   FROM (SELECT ID,  
              CAST ('<V>' + REPLACE(Value, ',', '</V><V>') + '</V>' AS XML) AS Value  
         FROM  YourTable
        ) AS A 
        CROSS APPLY Value.nodes ('/V') AS Split(a);

Sql query to create single row to multiple row

2 Answers2