Create a table from CSV columns in SQL Server without using a cursor

Question

Given a table:

|Name    | Hobbies                |
-----------------------------------
|Joe     | Eating,Running,Golf    |
|Dafydd  | Swimming,Coding,Gaming |

I would like to split these rows out to get:

|Name    | Hobby     |
----------------------
|Joe     | Eating    |
|Joe     | Running   |
|Joe     | Golf      |
|Dafydd  | Swimming  |
|Dafydd  | Coding    |
|Dafydd  | Gaming    |

I have completed this below (example is ready to run in SSMS), buy my solution uses a cursor which I think is ugly. Is there a better way of doing this? I am on SQL Server 2008 R2 if there is anything new which will help me.

Thanks

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Split]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].Split
go
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )


go

declare @inputtable table (
    name varchar(200) not null,
    hobbies varchar(200) not null
)

declare @outputtable table (
    name varchar(200) not null,
    hobby varchar(200) not null
)

insert into @inputtable values('Joe', 'Eating,Running,Golf')
insert into @inputtable values('Dafydd', 'Swimming,Coding,Gaming')

select * from @inputtable

declare inputcursor cursor for
select name, hobbies
from @inputtable

open inputcursor

declare @name varchar(255), @hobbiescsv varchar(255)
fetch next from inputcursor into @name, @hobbiescsv
while(@@FETCH_STATUS <> -1) begin

    insert into @outputtable
    select @name, splithobbies.s
    from dbo.split(',', @hobbiescsv) splithobbies

    fetch next from inputcursor into @name, @hobbiescsv 
end
close inputcursor
deallocate inputcursor

select * from @outputtable

Even though in your example each person has exactly three hobbies, I assume that each person can actually have 1 or more hobbies? — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Sep 7, 2010 at 16:52
@LittleBobbyTables yes, I should have mentioned the hobbies list can be of arbitrary length — amarsuperstar
– amarsuperstar, Commented Sep 7, 2010 at 17:28

Joe Stefanelli · Accepted Answer · 2010-09-07 17:09:27Z

Use a string parsing function like the one found here. The key is to use CROSS APPLY to execute the function for each row in your base table.

CREATE FUNCTION [dbo].[fnParseStringTSQL] (@string NVARCHAR(MAX),@separator NCHAR(1))
RETURNS @parsedString TABLE (string NVARCHAR(MAX))
AS 
BEGIN
   DECLARE @position int
   SET @position = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
         INSERT into @parsedString
         SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position)
         SET @position = charindex(@separator,@string,@position) + 1
      END
     RETURN
END
go

declare @MyTable table (
    Name char(10),
    Hobbies varchar(100)
)

insert into @MyTable
    (Name, Hobbies)
    select 'Joe', 'Eating,Running,Golf'
    union all
    select 'Dafydd', 'Swimming,Coding,Gaming'

select t.Name, p.String
    from @mytable t
        cross apply dbo.fnParseStringTSQL(t.Hobbies, ',') p

DROP FUNCTION [dbo].[fnParseStringTSQL]

codingbadger · Accepted Answer · 2010-09-07 17:01:11Z

4

Create this function in your DB:

CREATE FUNCTION dbo.Split(@origString varchar(max), @Delimiter char(1))     
returns @temptable TABLE (items varchar(max))     
as     
begin     
    declare @idx int     
    declare @split varchar(max)     

    select @idx = 1     
        if len(@origString )<1 or @origString is null  return     

    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@origString)     
        if @idx!=0     
            set @split= left(@origString,@idx - 1)     
        else     
            set @split= @origString

        if(len(@split)>0)
            insert into @temptable(Items) values(@split)     

        set @origString= right(@origString,len(@origString) - @idx)     
        if len(@origString) = 0 break     
    end 
return     
end

and then simply call it in your Select statement and use cross apply to join to the function

Select t.Name, 
       s.items as 'Hobby'
from dbo.MyTable as t
Cross Apply dbo.Split(t.Hobbies,',') as s

edited Sep 7, 2010 at 17:01

answered Sep 7, 2010 at 16:51

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

3 Comments

Denis Valeev Over a year ago

Nopers. You have to either cross apply or outer apply a TVF.

codingbadger Over a year ago

@Denis - thanks Denis. forgot I was dealing with Table Function there. Updated my answer now.

amarsuperstar Over a year ago

Thanks, cross apply is what I was after! WIll do some more reading on that now

Denis Valeev · Accepted Answer · 2010-09-07 17:54:29Z

4

Just do the following:

select *
from @inputtable
outer apply dbo.split(',', hobbies) splithobbies

edited Sep 7, 2010 at 17:54

answered Sep 7, 2010 at 16:56

Denis Valeev

6,03537 silver badges41 bronze badges

4 Comments

Philip Kelley Over a year ago

There is no native "split" function in SQL Server (which sucks).

Denis Valeev Over a year ago

@Philip The answer is given in the context of the question. Did you see that there's already a splitting function implemented at the very beginning of the question? So, it's alright.

amarsuperstar Over a year ago

is correct, the outer apply is the part I needed to know so this answer is perfectly valid

Philip Kelley Over a year ago

Fair enough. I just read the intro implying it was a typical cursor-based looping solution, saw everyone else's posted splitting functions, and assumed this was like all the other string-parsing questions posted on SO. Alas, votes (down or otherwise) cannot be reversed unless the original reply is modified; if you (add a space, change some punctuation), I'll reverse it.

gotqn · Accepted Answer · 2014-08-19 11:28:21Z

0

I generally prefer to use XML to split CSV list to table valued format. You can check this function:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

and the following article for more techniques showing how to do this. Then, you just need to use CROSS APPLY clause to apply the function.

answered Aug 19, 2014 at 11:28

gotqn

44k47 gold badges168 silver badges257 bronze badges

Collectives™ on Stack Overflow

Create a table from CSV columns in SQL Server without using a cursor

4 Answers 4

Comments

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related