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

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     RETURNENDgodeclare @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, ',') pDROP FUNCTION [dbo].[fnParseStringTSQL]

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 tCross Apply dbo.Split(t.Hobbies,',') as s 

Just do the following:

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