Here is my version:

create procedure ListToMultiColumnTable (@List Varchar(max), @ColumnCount int)

as

Begin

declare @tempList Varchar(max), @tempListInner Varchar(max), @listToInsert nvarchar(max)

Declare @ColumnNumber int,@substringCount int

set @templist=replace(@List,',',''',''')+''','''

While @templist<>''''

Begin

Set @ColumnNumber = 1

set @substringCount=0

set @tempListInner=@tempList

set @listToInsert=''

while @ColumnNumber <= @ColumnCount

begin

declare @ci int

set @ci=charindex(',',@tempListInner)

if @listToInsert<>''

set @listToInsert=@listToInsert+', '

set @listToInsert=@listToInsert+substring(@templistInner,1,@ci-1) +

' col'+convert(nvarchar,@columnnumber)

set @substringCount=@substringCount+@ci

set @tempListInner=substring(@tempListInner,

@ci+1,len(@tempListInner))

Set @ColumnNumber = @ColumnNumber + 1

end

if left(@templist,1)<>''''

begin

execute ('select '''+@listToInsert+ ' into ##tab')

Set @ColumnNumber = 1

while @ColumnNumber <= @ColumnCount

begin

execute('alter table ##tab alter column col'+@ColumnNumber+' nvarchar(max)')

Set @ColumnNumber = @ColumnNumber + 1

end

end

else

execute ('insert into ##tab

select '+@listToInsert)

set @templist=substring(@templist,@substringCount+1,len(@templist))

End

select * from ##tab

drop table ##tab

Return

End

go

--output example

declare @List varchar(1000)

set @List = '1,Tea,£1.50,2,Coffee,£1.75,3,Hot Chocolate,£2.05'

exec ListToMultiColumnTable @list,3