6

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.

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
Lokesh
  • 301
  • 3
  • 19
  • 1
    so you want to display `430 A319` if it is null?? – Dgan Oct 10 '14 at 06:26
  • Then NULL will be displayed in those columns – Lokesh Oct 10 '14 at 06:30
  • 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 Oct 10 '14 at 06:33
  • What do you exactly mean by col3 contains multiple values? What is the datatype of col3? – mreiterer Oct 10 '14 at 06: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 Oct 10 '14 at 06:36
  • The column with multiple values : is it comma seperated? Space seperated? if it has value "abcd123" do you want a record for every character?(ie: A , B, C , .. ) ? – Kristof Oct 10 '14 at 06:38
  • Why does that column store multiple values at all? – Tim Schmelter Oct 10 '14 at 06:38
  • Datatype is nVarchar, and the values are seperated by space. In this sense the values are multiple. – Lokesh Oct 10 '14 at 06:39
  • @Lokesh Provide Some more (>5) rows and Expected output – Dgan Oct 10 '14 at 06:39
  • What about moving col3 into an 1:n relation table and then do a simple join ? – mreiterer Oct 10 '14 at 06:44

1 Answers1

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)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • Worked like charm. Thanks a lot. – Lokesh Oct 10 '14 at 06:55
  • Just one more thing, can you add an explanation how the above code is working or any link where i can get the info. – Lokesh Oct 10 '14 at 06:56
  • 2
    @Lokesh This should help you.. http://oops-solution.blogspot.in/2011/10/sql-serverhow-to-split-comma-delimited.html – Pரதீப் Oct 10 '14 at 07:00
  • @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 – Arun Palanisamy Jul 31 '15 at 05:49
  • @Crazy2crack - Oracle does not support Cross Apply and Cross Join is not same as Cross Apply. Check this http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle – Pரதீப் Jul 31 '15 at 05:53
  • @Fireblade. Got it. Thank u :) – Arun Palanisamy Jul 31 '15 at 05:57
  • @MotoGP is it possible to use this in comma separated too? I tried your method and I get an error : http://stackoverflow.com/questions/36182905/transfer-comma-separated-values-in-one-column-to-different-rows-in-mysql – Rain Man Mar 23 '16 at 17:28
  • 1
    @RainMan - This answer specific to `SQL Server` will not work with `Mysql` – Pரதீப் Mar 26 '16 at 06:27
  • How to split multiple columns in this same way. I have tried, but i am failing on this. Please help me on this. Col1 Col2 Col3 Col4 ---------------------------------------------------------------------- Row 1 430 A319 N1160 N1336 78 90 Row 2 abc efg G3489 M5678 N5643 109 67 90 – kasim Apr 17 '20 at 10:06
  • @kasim Post it as a new question along with what have you tried so far. – Pரதீப் Apr 17 '20 at 10:16