Split a row based on a column value

Question

I am trying to split a row into two rows depending on the value in a cell on that row. For example, I have the following table structure (it's a temporary table without any keys, indexes or anything whatsoever and i can select the split results in another temporary table):

Col1  |  Col2
a     |  one
b     |  two
c     |  three
d     |  one two
e     |  one two

Then it it should be split into:

Col1  |  Col2
a     |  one
b     |  two
c     |  three
d     |  one
d     |  two
e     |  one
e     |  two

The problem is that I can't understand how to start, I found these two questions, which are similar, in my case it's an empty string instead:

Turning a Comma Separated string into individual rows

Split values over multiple rows

Look into this function http://www.sqlservercentral.com/Forums/Topic1445667-392-1.aspx — Mihai, Jan 22 '15 at 15:09
The same techniques that work with comma separated strings also work with space-separated strings. Just replace the comma with a blank space. — Tab Alleman, Jan 22 '15 at 15:23

score 3 · Accepted Answer · answered Jan 23 '15 at 01:49

I will do this using XML

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

SQLFIDDLE DEMO

Siyual · Answer 2 · 2015-01-22T15:19:33.200

You can use a UDF to handle the splitting of the strings and run it over each of your results. Here's something I got working fairly quickly:

Create FUNCTION [dbo].[fnSplit](@text varchar(MAX), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(    
  position int IDENTITY PRIMARY KEY,
  value varchar(MAX)   
)
AS
BEGIN

DECLARE @index int 
SET @index = -1 

WHILE (LEN(@text) > 0) 
  BEGIN  
    SET @index = CHARINDEX(@delimiter , @text)  
    IF (@index = 0) AND (LEN(@text) > 0)  
      BEGIN   
        INSERT INTO @Strings VALUES (@text)
          BREAK  
      END  
    IF (@index > 1)  
      BEGIN   
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   
        SET @text = RIGHT(@text, (LEN(@text) - @index))  
      END  
    ELSE 
      SET @text = RIGHT(@text, (LEN(@text) - @index)) 
    END
  RETURN
END

Test data:

Create Table #Temp 
(
    Col1 Varchar (20),
    Col2 Varchar (20)
)

Insert #Temp
Values ('a', 'one'), ('b', 'two'), ('c', 'three'), ('d', 'one two'), ('e', 'one two')

Query:

Select  Col1, Value[Col2]
From    #Temp   T
Cross Apply dbo.fnSplit(T.col2, ' ')

And results:

Col1    Col2
a       one
b       two
c       three
d       one
d       two
e       one
e       two

Please do not use (or post) a WHILE-based string splitter. That is one of the slowest / worst ways to split a string. Please replace that with a SQLCLR splitter (usually the best option, and there is an easy and free one available via the [SQL#](http://www.SQLsharp.com/) SQLCLR library -- which I wrote -- called `String_Split` and `String_Split4k`), or an inline tally-table-based version or even an XML-based splitter. Thanks :). — Solomon Rutzky, Jan 22 '15 at 15:26

Split a row based on a column value

2 Answers2

SQLFIDDLE DEMO

Linked