Split values over multiple rows

Question

Possible Duplicate:
Turning a Comma Separated string into individual rows

I have the following output from a stored procedure and was wondering the best way to split the values into multiple rows.

reference   name                            subjects       subjectstitle
LL9X81MT    Making and Decorating Pottery   F06,F27,F38       NULL

I need to trim the subjects field at the comma's and duplicate the information over three rows so the data would be as follows.

reference   name                            subjects       subjectstitle
LL9X81MT    Making and Decorating Pottery   F06       NULL
LL9X81MT    Making and Decorating Pottery   F27       NULL
LL9X81MT    Making and Decorating Pottery   F38       NULL

I'm using MS SQL Server 2008 to setup these SP's and just need some help on how to split the subjects field up.

Thanks,

How does the data get into that format? Can you not return it properly in the first place? — podiluska, Oct 31 '12 at 13:56
Simply use this: select refrence, sbj from TableX LATERAL VIEW explode(split(subjects, '[,]')) subjectTable AS sbj; — Reihan_amn, Mar 14 '19 at 21:21

Taryn · Answer 1 · 2012-10-31T15:57:47.243

You will want to use some sort of table-valued split function similar to 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;

You can then use outer apply to join with yourtable:

select t1.reference,
  t1.name,
  t1.subjectstitle,
  i.items subjects
from yourtable t1
outer apply dbo.split(t1.subjects, ',') i

Giving a result like this:

| 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 fiddle with Demo

If you want to do this without a split function, then 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 Fiddle with Demo

+1 : You *may* be better placed with `OUTER APPLY` though, just in case... — MatBailie, Oct 31 '12 at 13:57
@Dems you are probably right, updated thanks for the correction. — Taryn, Oct 31 '12 at 13:59
On this borad there is allready a topic on this and looks like was solved there http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Jester, Oct 31 '12 at 14:05
@Jester - You should flag the question as a duplicate. This is what you should do — Mahmoud Gamal, Oct 31 '12 at 14:08
So many duplicate questions getting answered on SO these days — codingbiz, Oct 31 '12 at 14:14

score 11 · Answer 2 · answered Oct 31 '12 at 15:30

11

This will do without a split function

SELECT T1.reference, T1.name, T2.my_Splits AS subjects, T1.subtitile
FROM
 (
  SELECT *,
  CAST('<X>'+replace(T.subjects,',','</X><X>')+'</X>' as XML) as my_Xml 
  FROM [yourTable] T
 ) T1
 CROSS APPLY
 ( 
 SELECT my_Data.D.value('.','varchar(50)') as my_Splits
 FROM T1.my_Xml.nodes('X') as my_Data(D)
 ) T2

answered Oct 31 '12 at 15:30

Kaf

33,101
7
58
78

2

Very nice. Works great. – smoore4 Oct 14 '13 at 18:10
This is great. Is there a way to also add a sequence number to go along with the split values? – aaaantoine Aug 16 '17 at 14:30
I think figured it out. Add an additional column right above "my_splits" in T2: ```row_number() over (order by my_data.D) as sequence_num,``` – aaaantoine Aug 16 '17 at 14:33
much more performant. I went from 1700 reads to 28. Makes an ugly execution plan but I don't care! – LarryBud Mar 19 '19 at 19:10

Split values over multiple rows

2 Answers2

Linked

Related