SQLTeam.com | Weblogs | Forums

Parse the values into multiple columns

Greetings,

I want a query to parse the tagvalue columns by "/" separator to multiple columns depending on the number of time the separator "/" exists in the tagvalue column and there is no limit of the values.

id  tagvalue
--- ---------  
1   asto/cand?/questions
2   bog/col1/col2.com/col3./col4


Expected output

id   tagvalue1 tagvalue2  tagvalue3  tagvalue4 tagvalue6
---  --------- ---------  ---------   --------  --------
1      asto    cand?       questions   null      Null
2       bog    col1        col2.com     col3.    col4

Thanks for your help in advance !!

;with src
as
(
	select  tagvalues, Item	, id
		from
	(
		Select distinct 'tagvalue' + cast(ItemNumber as varchar(50)) as tagvalues,
 Item, ItemNumber, id
			From @shisham sd
			Cross apply DelimitedSplit8K( sd.tagvalue,'/') 
	) p
)
select *
 from src
PIVOT 
(
	max(item)
	FOR tagvalues in ([tagvalue1], [tagvalue2], [tagvalue3], [tagvalue4], [tagvalue5])
) a 

From @JeffModen

1 Like

Great article.