You want to use some sort of table function split like this:
create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1)) returns @temptable TABLE (items varchar(MAX)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end;
Then you can use outer apply to join your table:
select t1.reference, t1.name, t1.subjectstitle, i.items subjects from yourtable t1 outer apply dbo.split(t1.subjects, ',') i
Give the result as follows:
| REFERENCE | NAME | SUBJECTSTITLE | SUBJECTS | ------------------------------------------------------------------------ | LL9X81MT | Making and Decorating Pottery | (null) | F06 | | LL9X81MT | Making and Decorating Pottery | (null) | F27 | | LL9X81MT | Making and Decorating Pottery | (null) | F38 |
See SQL script for a demo
If you want to do this without the split function, you can use CTE:
;with cte (reference, name, subjectstitle, subjectitem, subjects) as ( select reference, name, subjectstitle, cast(left(subjects, charindex(',',subjects+',')-1) as varchar(50)) subjectitem, stuff(subjects, 1, charindex(',',subjects+','), '') subjects from yourtable union all select reference, name, subjectstitle, cast(left(subjects, charindex(',',subjects+',')-1) as varchar(50)) , stuff(subjects, 1, charindex(',',subjects+','), '') subjects from cte where subjects > '' ) select reference, name, subjectstitle, subjectitem from cte
See SQL script for a demo