Split and Pivot a Field

Transact-SQL
1

I need to split comma separated values and pivot them to create a 2 column mapping view.

The hard part: The first item in each list is the mapping key for all items in that list (including itself).

For example, given this list (in one column):

Related_Fruits
Banana, Watermelon, Kiwi
Apple
Mango, Orange

...this is the desired output (a mapping table):

Fruit_Parent Fruit_Child
Banana Banana
Banana Watermelon
Banana Kiwi
Apple Apple
Mango Mango
Mango Orange

Here is some test ddl/data

CREATE TABLE #test(Related_Fruits varchar(50)) 
insert into #test Values ('Apple')
insert into #test Values ('Banana, Watermelon, Kiwi')
insert into #test Values ('Mango, Orange')
select * from #test

This is as close as I've gotten so far, but the first column includes ALL values in the list, not just the first one. And when I try to parse out just the first value (substring and charindex), then the cross apply and string_split don't work. :frowning:

Also, there are spaces after the commas in the list, which indent each child by one space (doesn't show in this forum due to formatting) which also need to be removed.

Select *
From #test
Cross Apply String_Split(Related_Fruits,',')

Result:

Related_Fruits value
Apple Apple
Banana, Watermelon, Kiwi Banana
Banana, Watermelon, Kiwi Watermelon
Banana, Watermelon, Kiwi Kiwi
Mango, Orange Mango
Mango, Orange Orange

Any suggestions?

2 
SELECT TRIM(SUBSTRING(CONCAT(Related_Fruits,','),1,CHARINDEX(',',CONCAT(Related_Fruits,','))-1)) fruit_parent
, TRIM(value) AS fruit_child
FROM #test
CROSS APPLY STRING_SPLIT(Related_Fruits,',')
1 Like
3

That would work but the OP is using 2012.

EDIT: Interesting. The OP listed this as 2012 and yet the STRING_SPLIT() solution has been marked as the answer.

4

yes this is solved

just exercising my noodles thinking of a different way to do this

create data script

drop table #Related_Fruits

create table #Related_Fruits ( Related_Fruits varchar(100))

insert into #Related_Fruits select 'Banana, Watermelon, Kiwi'
insert into #Related_Fruits select 'Apple'
insert into #Related_Fruits select 'Mango, Orange'

select * from #Related_Fruits

; with cte as 
   (  select ROW_NUMBER() over(order by Related_Fruits) as rn,* from #Related_Fruits )
  ,cte_rn as  
   ( select row_number() over(partition by rn order by rn,related_fruits) as rn1,* from cte CROSS APPLY STRING_SPLIT(Related_Fruits,',') )
   select  
       a.value 
     , b.value as value1  
   from 
     (select * from cte_rn where rn1 = 1) a 
	    join 
     (select * from cte_rn ) b 
	     on a.rn = b.rn

image