Suppose you have string in csv format and want to split into seperate columns. You can use parsename function as long as you have maximum four values. The following would work for any number of values
declare
@s varchar(2000),@data varchar(2000)
select
@s='this,is,test'
select
@data=''''+replace(@s,',',''',''')+''''
exec
('select '+@data)