Splitting Comma separated values in columns to multiple rows in Sql Server

Question

My table has three columns. One of the columns [Col3] has multiple values. So, when I make a select command on the table :

Select col1, col2, col3 from MyTable

It gives me the below result:

         Col1       Col2         Col3
         ------------------------------
Row 1    430        A319         N1160 N1336
Row 2    abc        efg          G3489 M5678 N5643

If there any way to get the output as:

         Col1       Col2         Col3
         ------------------------------
Row 1    430        A319         N1160
Row 2    430        A319         N1336
Row 3    abc        efg          G3489
Row 4    abc        efg          M5678
Row 5    abc        efg          N5643

Like if the column has multiple values, then a new row is displayed corresponding to each value in the column and other columns should contain the duplicated data.

I hope I am pretty clear with the problem.

It seems you misunderstood Ganesh_Devlekar. How do you know that you want to show 430 for col1 in row 2? Is it always 430 for a NULL in col1 and always A319 for a NULL in col2? — Thorsten Kettner
– Thorsten Kettner, Commented Oct 10, 2014 at 6:33
What do you exactly mean by col3 contains multiple values? What is the datatype of col3? — mreiterer
– mreiterer, Commented Oct 10, 2014 at 6:36
I think i again do not get the point. My table has a single row and one of the columns has multiple data. Requirement is to get as many rows as the number of multiple values in the column and the other columns should contain the same data(whether any value or Null). I hope it is clear now. — Lokesh
– Lokesh, Commented Oct 10, 2014 at 6:36

Pரதீப் · Accepted Answer · 2014-10-10 06:47:33Z

19

SELECT col1,
       col2,
       Split.a.value('.', 'VARCHAR(100)') col3
FROM   (SELECT col1,
               col2,
               Cast ('<M>' + Replace(col3, ' ', '</M><M>') + '</M>' AS XML) AS Data
        FROM   [table]) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a)

answered Oct 10, 2014 at 6:47

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Lokesh Over a year ago

Worked like charm. Thanks a lot.

Lokesh Over a year ago

Just one more thing, can you add an explanation how the above code is working or any link where i can get the info.

Pரதீப் Over a year ago

@Lokesh This should help you.. oops-solution.blogspot.in/2011/10/…

Arun Palanisamy Over a year ago

@Fireblade: Can you pls tell me how to do the same thing it in Oracle. I have replaced CROSS APPLY with 'CROSS JOIN'. But it is not working

Pரதீப் Over a year ago

@RainMan - This answer specific to SQL Server will not work with Mysql

|

Collectives™ on Stack Overflow

Splitting Comma separated values in columns to multiple rows in Sql Server

1 Answer 1

9 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Linked

Related