Edit Question - KnowledgeHub

Title Body

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 in

Tags (comma-separated) Cancel