Convert comma seperate string into individual rows

Question

Hi Team, I have a requirement that need to convert comma separated string into individual records in DB table. Below I mentioned sample data. From below screenshot input columns C3&C4 are independent columns, where as C5& c6 are dependent on each other, because of this it perform cross apply and display as an output. Can some one help on this requirement. ![19614-image.png][1] [1]: /api/attachments/19614-image.png?platform=QnA

Answer 1

CREATE TABLE test  
(C1 int
,C2 varchar(20)
,C3 varchar(20)
,C4 varchar(20)
,C5 varchar(20)
,C6 varchar(20)
,C7 varchar(20)
)

insert into test 
values(1,'ABC','A,B,C','S,D','W,E,R','V,O','')

-- option1 string_split
;with mycte3 as (
select C1,C2,value c3, row_number() over(order by C1) rn from test
Cross apply string_split(C3,',') a
)
, mycte4 as (
select C1,C2, value as C4, row_number() over(order by C1) rn from test
Cross apply string_split(C4,',') a
)
, mycte56 as (
Select c1,c2,a.value as c5, b.value as c6 
,row_number() over(order by C1) rn
from test
Cross apply string_split(C5,',') a
Cross apply string_split(C6,',') b
)

select m0.C1,m0.C2,m3.C3,m4.C4,m0.C5,m0.C6  from mycte56 m0 
left join mycte3 m3 on m0.c1= m3.c1 and m0.c2= m3.c2 and m0.rn= m3.rn
left join mycte4 m4 on m0.c1= m4.c1 and m0.c2= m4.c2 and m0.rn= m4.rn

-- option2 json
;with mycte3 as (
select  C1, C2,   c.value AS C3 ,c.[key] as rn
from test 
cross apply openjson(('["'+ REPLACE(C3, ',', '","') + '"]')) c
)
, mycte4 as (
select  C1, C2,   d.value AS C4 ,d.[key] as rn
from test 
cross apply openjson(('["'+ REPLACE(C3, ',', '","') + '"]')) d
)
,mycte56 as (
select   C1, C2,   a.value AS C5,  b.value AS C6
,row_number()Over(order by C1,C2,a.[key],b.[key]) rn

from test 
cross apply openjson(('["'+ REPLACE(C5, ',', '","') + '"]')) a
cross apply openjson(('["'+ REPLACE(C6, ',', '","') + '"]')) b
)

select m0.C1,m0.C2,m3.C3,m4.C4,m0.C5,m0.C6  from mycte56 m0 
left join mycte3 m3 on m0.c1= m3.c1 and m0.c2= m3.c2 and m0.rn= m3.rn
left join mycte4 m4 on m0.c1= m4.c1 and m0.c2= m4.c2 and m0.rn= m4.rn


--option3 other split udf 
 /*

 */

drop   TABLE test

Answer 2

Hi Praveen，

I quoted a function(dbo.SplitSubString ) written by someone else, which can easily separate various strings according to user needs，please refer to:

CREATE TABLE test    
 (C1 int  
 ,C2 varchar(20)  
 ,C3 varchar(20)  
 ,C4 varchar(20)  
 ,C5 varchar(20)  
 ,C6 varchar(20)  
 ,C7 varchar(20)  
 )  
 insert into test   
 values(1,'ABC','A,B,C','S,D','W,E,R','V,O','')  
  
 create function dbo.SplitSubString   
(   
 @Expression varchar(8000)   
,@Delimiter varchar(100)   
,@ int   
)   
returns varchar(8000)   
as   
begin   
  
  
declare @p int   
set @p = CharIndex(@Delimiter,@Expression)   
if @p > 0  
begin  
   set @p = @p + len(@Delimiter) - 1  
end  
declare @i int   
set @i = 1   
while @i < @   
begin    
   set @i = @i + 1  
   set @Expression = substring (@Expression, @p + 1,len(@Expression) - @p )   
   set @p = CharIndex(@Delimiter,@Expression)  
   if @p > 0  
   begin  
      set @p = @p + len(@Delimiter) - 1  
   end  
   else  
   begin  
      break  
   end  
end   
  
declare @s varchar(1000)   
if @p = 0 and @i = @  
begin   
   set @s = @Expression  
end   
else if @i = @   
begin   
   set @s = substring(@Expression, 1,@p - len(@Delimiter))   
end   
return @s  
end   
  
GO  
  
  
with cte   
as(select c1,TestDB.dbo.SplitSubString(c2,',',1) as C2,  
             TestDB.dbo.SplitSubString(c3,',',1) as C3,  
             TestDB.dbo.SplitSubString(c4,',',1) as C4,  
  TestDB.dbo.SplitSubString(c5,',',1) as C5,  
  TestDB.dbo.SplitSubString(c6,',',1) as C6 from test  
   union all  
   select c1,TestDB.dbo.SplitSubString(c2,',',2) as t2,  
             TestDB.dbo.SplitSubString(c3,',',2) as t3,  
  TestDB.dbo.SplitSubString(c4,',',2) as t4,  
  TestDB.dbo.SplitSubString(c5,',',1) as t5,  
  TestDB.dbo.SplitSubString(c6,',',2) as t6 from test  
   union all  
   select c1,TestDB.dbo.SplitSubString(c2,',',2) as t2,  
             TestDB.dbo.SplitSubString(c3,',',3) as t3,  
  TestDB.dbo.SplitSubString(c4,',',3) as t4,  
  TestDB.dbo.SplitSubString(c5,',',2) as t5,  
  TestDB.dbo.SplitSubString(c6,',',1) as t6 from test  
   union all  
   select c1,TestDB.dbo.SplitSubString(c2,',',2) as t2,  
             TestDB.dbo.SplitSubString(c3,',',4) as t3,  
  TestDB.dbo.SplitSubString(c4,',',4) as t4,  
  TestDB.dbo.SplitSubString(c5,',',2) as t5,  
  TestDB.dbo.SplitSubString(c6,',',2) as t6 from test  
   union all  
   select c1,TestDB.dbo.SplitSubString(c2,',',2) as t2,  
             TestDB.dbo.SplitSubString(c3,',',5) as t3,  
             TestDB.dbo.SplitSubString(c4,',',5) as t4,  
             TestDB.dbo.SplitSubString(c5,',',3) as t5,  
             TestDB.dbo.SplitSubString(c6,',',1) as t6 from test  
   union all  
   select c1,TestDB.dbo.SplitSubString(c2,',',2) as t2,   
             TestDB.dbo.SplitSubString(c3,',',6) as t3,  
  TestDB.dbo.SplitSubString(c4,',',6) as t4,  
  TestDB.dbo.SplitSubString(c5,',',3) as t5,  
  TestDB.dbo.SplitSubString(c6,',',2) as t6 from test)  
  
select * from cte  
  
drop table test  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo